Tips and techniques

EMACS

I have a made a Emacs mode file for RPG. It's very rudimentary, but it is better than SEU. And it enables me to use CVS directly. Usually I transfer the source using getfrmrpgsrc, add to CVS (C-x-v-v), edit, replace with puttorpgsrc, compile and test on the iSeries.
Then I check it into the repository (C-x-v-v) with comments.
Just have env. variables CVSROOT and CVS_RSH properly set.

SQL

Need 10000 numbers in a file?:
This code is spesifique for IBM iSeries, I think.

 CREATE TABLE NBR10000 (Nbr DEC(5, 0) NOT NULL GENERATED ALWAYS
   AS IDENTITY(START WITH 1 INCREMENT BY 1 NO CACHE), Dummy INT);
INSERT INTO NBR10000 (Dummy) SELECT RRN(SOME_LARGE_FILE) 
   FROM SOME_LARGE_FILE
   FETCH FIRST 10000 ROWS ONLY;

SQL

Test if an alpha coloumn is numerical:
TRANSLATE(Char6, 'NNNNNNNNNNNNNNNX', '1234567890 <>.,N')='NNNNNN'

SQL

Command for running a SQL statement:

CLP: DB2CL

PGM (&SQLCMD)
DCL &SQLCMD *CHAR 512
/* Copyright 2005 Knut Berg */
    MONMSG CPF0000       
    CRTSRCPF   FILE(QTEMP/SQLSRC) RCDLEN(600) MBR(SQL1)
    OVRDBF     FILE(SQLSRC) TOFILE(QTEMP/SQLSRC) MBR(SQL1)
    CALL   DB2RPG  &SQLCMD                                
    RUNSQLSTM  SRCFILE(QTEMP/SQLSRC) SRCMBR(SQL1) +       
               COMMIT(*NONE) NAMING(*SYS)          
ENDPGM
--
CMD: DB2RUN

CMD  PROMPT('Run a SQL statement')
     PARM  KWD(SQLCMD) TYPE(*CHAR) LEN(512) PROMPT('SQL-stmt')
--
RPGLE: DB2RPG
      *Copyright 2005 Knut Berg
     H
     FSqlSrc    UF A F  600        Disk
     D SqlCmd          S            512
     ISqlSrc    NS  01
     I                                  1    6 2SRCSEQ
     I                                  7   12 0SRCDAT
     I                                 13  600  SRCDTA
     C     *Entry        PList
     C                   Parm                    SqlCmd          512
     C                   DoU       %eof(SqlSrc)
     C                   Read      SqlSrc
     C                   If        not %eof(SqlSrc)
     C                   Except    DelRec
     C                   EndIf
     C                   EndDo
     C                   Eval      SrcDta=SqlCmd
     C                   Except    NewRec
     C                   Eval      *InLr=*on
     OSqlSrc    EADD         NewRec
     O                       SRCSEQ
     O                       SRCDAT
     O                       SRCDTA
     OSqlSrc    EDEL         DelRec

SQL

Using WITH, distinct, max:
We have a table containing periodical data. Each entry has some codes which may change every period.

To make a report listing all periods horizontally, I suggest you use the WITH clause.
To get the last code used for each key in the selection, you may use the code for tmp1 - temporary table 1.
Tmp2 and tmp3 just select periodical data.
Tmp4 selects unique key (which you may already have in a parent table, of course).

WITH 
tmp1 (key1, code1) as (
 select  n1.key, n1.code 
    from table1 n1
	where n1.sel = ... 
	and n1.mnth =
    (select max(n2.mnth) from table1 n2
    where n2.sel=n1.sel and n2.key=n1.key)
),
tmp2 (jan, key2) as (
 select case when cnt is null then 0 else cnt end , key
 from table1 
	where sel= ...
	and mnth=1 
),
tmp3 (feb, key3) as (
 select case when cnt is null then 0 else cnt end , key
 from table1
        where sel= ...
        and mnth=2
),
.
.
.
tmp4 (key4) as (
 select distinct key from table1
	where sel= ...
)
select key4, jan, feb, code1
	from tmp4 
	left outer join  tmp1 on key1=key4
        left outer join  tmp2 on key2=key4
        left outer join  tmp3 on key3=key4
order by ...

SQL

Ever got binary rubbish in a character key field ? Try this to locate it..
select * from table where keycol like concat(x'F0F000', '%')
if your key looks like 00x'0 (this is EBCDIC)

Update a set of rows using column values fetched from another table.

UPDATE TableA SET  ColA= 
 (SELECT  ColB FROM  TableB b WHERE
      KeyA=KeyB) 
 WHERE EXISTS 
  (SELECT ColB
      FROM TableB WHERE KeyA=KeyB)

DB2
A db2 how to for linux.
Start a xterm from PASE on iSeries, display window on linux client
On you client you must run xhost + to open  the clients X-server for connections.

Log on the iSeries and run: call qp2term to enter the PASE shell.

Then export DISPLAY=x.x.x.x:0.0, substitute x.x.x.x with your client's ip adress.

Simply typing xterm & or aixterm & should bring up a terminal window at your desktop.

Didn't work? Maybe you must configure your X-server to listen for external connection. Check for xserverrc ...
Linux ip adresse
To get a listing of ip adresses like ifconfig you can use netstat -ie
Linux Broken initrd on SuSE
Oops, I broke my initrd on a SuSE 10.1 box. Fixed it this way: 

Boot the rescue system from CD/DVD. Enter root at login. Mount root partion like: mount /dev/hda2 /mnt
Then: "cd /mnt" and "chroot ." Next: mkinitrd -m "reiserfs" / 
This should fix it all, I needed the reiserfs module, you may have different needs!
"sync", "exit", "cd", "umount /mnt", pick out the CD/DVD, "init 6" and rock and roll again :-)
Linux output to a window
To display output from a command line tool graphically in KDE you could try this: 
ls -l | kdialog --textbox /dev/stdin 640 800