Trucs et Astuces - AS400 - Tips and Tricks

page d'accueil   Boite à outils

  Table des matières       table of Contains

• dictionnary
• export data
• first rows only
• horizontal count
• litteral casting
• null when update
• qsh db2
• qsqptabl
• Set result sets
• To CSV
• With
• Sql tips and tricks
• Visual Explain
• Exec SQL to run an SQL stack, such as RUNSQLSTM but with parameters
• Execute SQL to run an SQL statement from a CLP program
• User Defined table Function
• SQL table function

QSQPTABL

la table sql AS400 qui fait toujours une ligne s'appelle qsys2.qsqptabl. Et aussi sysibm.sysdummy1. Pratique pour tester les fonctions intégrées

RETRIEVE FIRST RECORDS

sql limité : 'select * from makep fetch first 10 rows only'

CREATE TABLE WITH DICTIONNARY

création de table par référence à une table dictionnaire : create table as (select ... from ...,...) with no data ; label on ...

SPEED UP SQL

Glané sur le web 

à http://search400.techtarget.com/tip/1,289483,sid3_gci1097043,00.html?FromTaxonomy=%2Fpr%2F2f9

Speed up SQL subselects
Drew Dekreon
10 Jun 2005
An SQL subselect picks records based on whether a value appears in another select -- a simple example would be: 
select * from orders where partno in (select partno from outofstock) . 
My master file has two keys so to do a subselect I have to to combine them. Key1 is 9s0, key2 is 3s0 so I have been selecting like this:  
select * from master where key1*1000+key2 in (select k1*1000+k2 from billfile)

This is very slow, compared to an inner join. I recently found that if I converted the combined key to a string, response is as fast as an inner join (apparently the iSeries is much better with characters than math): 

select * from master where cast(key1 as char(9))||cast(key2 as char(3))in (select cast(k1 as char(9))||cast(k2 as char(3)) from billfile)

 The || is string concatenation in SQL; cast() transforms one data type to another.

CONVERT TO CSV

at http://search400.discussions.techtarget.com/WebX?msgInContext@@.ee84636/5760 

Have a Question on CPYTOIMPF converting to csv. For the Numeric fields, the leading Zeros are removed (which is fine).. but the trailing zeros are shown as blanks & the comma separator is shown at the length of the Numeric field.
Is there a way to supress trailing Zero/Blank to show the Numeric data

Thanks in Advance.
Kris

select
  '<record><mydate>' concat
  char(current date,iso) concat '</mydate><mytime>' concat
  char(current time, iso) concat '</mytime><mystamp>' concat
  char(current timestamp) concat '</mystamp><user>' concat
  user concat '</user><mydecimal>' concat
  char(decimal(days(current date), 7, 0)) concat '</mydecimal><myzoned>' concat
  char(zoned(days(current date), 7, 0)) concat '</myzoned><mydouble>' concat
  char(double(days(current date) ** 12 )) concat '</mydouble><mybigint>' concat
  char(bigint(days(current date) ** 3 )) concat '</mybigint>
from qsys2/qsqptabl

MISSING : XML HEADER .......................................AJOUTER LES ENTETES XML

See also QSH DB2, its exports are CSV

Horizontal count

from http://www.itjungle.com/fhg/fhg102605-story01.html

select chgcod, 
 sum(case state when 'NY' then 1 else 0 end) as New_York, 
 sum(case state when 'MN' then 1 else 0 end) as Minnesota,
 sum(case when state not in ('NY','MN') 
 then 1 else 0 end) as Other 
from qiws/qcustcdt 
group by chgcod 
order by chgcod 

CHGCOD NEW_YORK MINNESOTA OTHER 
 1 2 0 3 
 2 1 0 3 
 3 0 2 1 

Avoid null when update many records

found athttp://www.systeminetwork.com/isnetforums/showthread.php?t=45809

Update filea as a
 set (clasaa,slmnaa) =
    (select classbb,slmnbb from fileb as b
       where b.divnbb   = a.divnaa
       and     b.pnumbb = a.pnumbb),
 where exists
    (select * from fileb as b
       where b.divnbb   = a.divnaa
       and     b.pnumbb = a.pnumbb);
 

QSH DB2

Found on the web :

at http://www.search400.com/tip/1,289483,sid3_gci1098569,00.html?track=NL-177&ad=521380

CL has been able to do a few forms of writing to files for many years. CL can call the Sort (QLGSORT) API and pass records into the API through the input buffer -- the API can send those to output files which can be copied/appended to other files. CL can call QM queries to effect SQL INSERTs to files.

 But in Version 5 of OS/400, CL got even closer through the QShell DB2 utility (see example #1). And with a little research, you can see that the DB2 utility is a symbolic link to program QZDFMDB2 (see example #2).

Still not perfect, but pretty workable.

#1
QSH CMD('db2 "INSERT INTO QIWS.QCUSTCDT (CUSNUM, LSTNAM, INIT) VALUES(876543, ''Liotta'', '')"')
#2
CALL QZDFMDB2 ('INSERT INTO TOML.QCUSTCDT (CUSNUM, LSTNAM, INIT) VALUES(876543, ''Liotta'', ''T'') ')

- Tom Liotta

QZDFMDB2 is a pretty and native solution, but there is a little processor overhead to load and close the QSH.Will have to check if errors can be MONMSGed. Seems a better solution to use it from QSH : QSH interface is published, QZDFMDB2 is an IBM internal tool, interface can change without advertissement.

An other pretty property of QSH DB2 : it's standard output when running a SELECT is a CSV file. Have found a sample at iseries network, lost the link

Export data with SQL

Here is a complete sample based on previous extract

first step : create a simple database
CREATE TABLE jpl/TENLIBS AS ( SELECT DIOBLI, DIOBNM, DIOBTP, DIOBAT, DIOBTX, DIOBSZ FROM QUSRSYS/QAEZDISK WHERE DIOBTP = 'LIB'
FETCH FIRST 10 ROWS ONLY) WITH DATA 

then with STRSQL, work with the file :

select * from jpl/tenlibs +F4
You here can see that the new table has reclaimed text and column heading from source file
Séq Zone Fichier Texte 
DIOBLI TENLIBS Object Library 
DIOBNM TENLIBS Object 
DIOBTP TENLIBS Object Type 
DIOBAT TENLIBS Object Attribute 
DIOBTX TENLIBS Text Description 
 DIOBSZ TENLIBS Object Size 

....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10....+...11....+... Object Object Object Object Text Description Object Library Type Attribute Size QSYS QSYSLOCALE LIB 53.248 QSYS QGDDM LIB 208.896 QSYS ERLCOM6 LIB Données logiques sur MAPICS XA V6 90.112 QSYS AFFLIB2G LIB 802.816 QSYS ACS2624 LIB ACSAPPLY - SH14446 XAR6/2624 Library 245.760 QSYS TIFFLIB LIB MMAIL .tif conversion to .pdf 90.112 QSYS QSYS2924 LIB 3.239.936 QSYS QGPL LIB General Purpose Library 3.833.856 QSYS QHTTPSVR LIB 888.832 QSYS TUNELIB LIB Calcul de la taille des bibliothèques (DDN) 90.112 ******** Fin de données ******** 

Now, build a client access DTF to read the file, the simplest dtf : choose the file and that's all (tenlibs.dtf). Open it from Client-Access excel-addin tool bar : tenlibs.xls

Client Access DTF + excel : colum heading is column name
DIOBLI DIOBNM DIOBTP DIOBAT DIOBTX DIOBSZ

And direct 400 to excel also

SQL2SYLK SQL('select * from jpl/tenlibs') IFSFILE('TenLibs.slk') 

TenLibs.slk

Now, work on colum heading  and column text Nota : column heading is a one-string value, 60 chars length, showed by SQL as 3 * 20 chars

LABEL ON COLUMN JPL/TENLIBS (
 DIOBLI IS 'Object Library colhdg line 2 colhdg line 3'
, DIOBNM IS 'Object name'
, DIOBTP IS 'Object type'
, DIOBAT IS 'Object attribute'
, DIOBTX IS 'Description'
, DIOBSZ IS 'Object size') 

 LABEL ON COLUMN JPL/TENLIBS (
 DIOBTP TEXT IS 'Type'
, DIOBAT TEXT IS 'Attribute'
, DIOBTX TEXT IS 'Description'
, DIOBSZ TEXT IS 'Size') 

select * from jpl/tenlibs +F4

Séq Zone Fichier Texte DIOBLI TENLIBS Library DIOBNM TENLIBS Object DIOBTP TENLIBS Type DIOBAT TENLIBS Attribute DIOBTX TENLIBS Description DIOBSZ TENLIBS Size 

....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10....+...11....+...12....+...13.
Object Library Object name Object type Object attribute Description Object siz
colhdg line 2 colhdg line 3 QSYS QSYSLOCALE LIB 53.24
QSYS QGDDM LIB 208.89
QSYS ERLCOM6 LIB Données logiques sur MAPICS XA V6 90.11
QSYS AFFLIB2G LIB 802.81
QSYS ACS2624 LIB ACSAPPLY - SH14446 XAR6/2624 Library 245.76
QSYS TIFFLIB LIB MMAIL .tif conversion to .pdf 90.11
QSYS QSYS2924 LIB 3.239.93
QSYS QGPL LIB General Purpose Library 3.833.85
QSYS QHTTPSVR LIB 888.83
QSYS TUNELIB LIB Calcul de la taille des bibliothèques (DDN) 90.11
******** Fin de données ******** 

re run the client acces dtf (tenlibs.dtf) Client Access DTF + excel : column heading continues to be the column name : Client Access don't take care of column heading

DIOBLI DIOBNM DIOBTP DIOBAT DIOBTX DIOBSZ

But direct 400 to excel yes : SQL2SYLK SQL('select * from jpl/tenlibs') IFSFILE('TenLibsLabel.slk') TenLibsLabel.slk  

Update the DTF : rename the columns

...
Select=DIOBLI "Library",DIOBNM "Object",DIOBTP "type",DIOBAT "attribute",DIOBTX "text description",DIOBSZ "size"
...

Now, Client Access shows correct headings tenlibs.xls

With

declare lcdr50K2 cursor for with t1 as ( SELECT VNDNR , count(*) nbval, house FROM lcdartp WHERE VALART ='V' and shedul = ' ' GROUP BY vndnr, house ), t2 as ( SELECT VNDNR , count(*) nbart, house FROM lcdartp GROUP BY vndnr, house ), t3 as ( select t1.vndnr , t1.house from t1 join t2 on t1.vndnr = t2.vndnr and t1.house = t2.house where nbval = nbart) select t4.VNDNR, t4.ITNBR, t4.HOUSE , coalesce(t5.lvlpsk, '?') from lcdartp t4 exception join t3 on t3.vndnr = t4.vndnr and t3.house = t4.house left join levf t5 on t4.vndnr = t5.lvlevi where valart='V' and shedul = ' ' and t5.lvlpsk in ('E', 'X', 'F') union all
select t1.vndnr , cast('*ALL' as char(15)) itnbr, t1.house , coalesce(t5.lvlpsk, '?') from t1 join t2 on t1.vndnr = t2.vndnr and t1.house = t2.house
left join levf t5 on t1.vndnr = t5.lvlevi < BR > where nbval = nbart and t5.lvlpsk in ('E', 'X', 'F') 

How SQL cast litteral to internal variables

litteral or constants ? at first, it's the same thing

select myfunc('string') from qsys2.qsqptabl is equal to select myfunc(cast('string' as varchar)) from qsys2.qsqptabl

string litterals are automatically casted to varchar

select myfunc(0) from qsys2.qsqptabl is equal to ?

Built'in SQL Data type groups
datalink : datalink
datetime : time timestamp date
character string : char varchar clob
graphic string : graphic vargraphic dblob
binary string : binary varbinary blob
numeric : smallint integer bigint decimal numeric real float
rowid : rowid

details and samples :  in sql reference (rbafzmst.pdf) chapter Language elements / Constants
up to 19 digits, no decimal point, minus sign -> an integer (bigint ! 20I 0)
2 numbers separated by a E : float 4F or 8F
up to 63, decimal point -> decimal
char -> varying
graphic -> varying
bin -> varying
time timestamp date -> time timestamp date

http://www.itjungle.com/fhg/fhg031704-story02.html shows RPG declarations for SQL data types

Sample of code to return a data set

RPG, dynamic WHERE, fixed SELECT

In this sample, the format of the returned rows are fixed at compile time

    , *  this sample shows how to code in RPG the inside-code of a
    , *  sql procedure that returns a result set.
    , *
    , *  based on data from qiws/qcustcdt.
    , *  Take care of having QIWS in your library list
    , *
    , *  Create Procedure udtfproc1 ()
    , *         Dynamic Result Sets 1
    , *         Language RPGLE
    , *         not deterministic
    , *         Reads SQL Data
    , *         external name udtfr1
    , *
    , * compilation :
    , *
    , * test : with iSeries Navigator, myserver/database/mylocalrdb/schemas, in low right corner
    , *        run sql script : call udtfproc1()
    , *
     h debug datedit(*ymd) datfmt(*iso-) decedit('0.')

    , *================================================================
    , * sample of program for sql procedure returns a resultset
    , *================================================================
    , * define the output recordset, occursing the external output record format
     d recordset     e ds                  extname(qcustcdt)
     d                                     occurs(32765)                        it's the max value
     d count           s             10u 0 inz(0)
     d MySFW           s          32740    varying
      /free
       EXEC SQL
          Set Option Commit = *NONE
          , CloSQLCsr = *ENDMOD
          , DatFmt = *ISO
          , TimFmt = *ISO
          , Naming = *SYS
          ;
       mySFW = 'select * from qiws/qcustcdt';
       EXEC SQL prepare jpl021p2 from : mysfw ;
B01    if sqlcod <> *zero;
          exsr *pssr ;
E01    endif;
       EXEC SQL declare jpl021K2 cursor for jpl021p2  ;
       EXEC SQL open jpl021k2  ;
B01    if sqlcod <> *zero;
          exsr *pssr ;
E01    endif;
B01    for count=1 to 32765;
          %occur (recordset)=count;
          EXEC SQL fetch jpl021k2 into : recordset ;
B02       if sqlcod = 100;
             count-=1;
             leave;
E02       endif;
B02       if sqlcod <> *zero;
             exsr *pssr;
E02       endif;
E01    endfor;
       EXEC SQL close jpl021k2 ;
       EXEC SQL Set result sets array : recordset for : count rows ;
       *inlr = *on ;
B01    begsr *pssr ;
          dump ;
E01    endsr '*CANCL';
      /end-free

PLSQL, fixed SQL statement

    CREATE PROCEDURE LAMONTRE1/READRSP1 ()
    LANGUAGE SQL
    DYNAMIC RESULT SET 1
    DETERMINISTIC
    READS SQL DATA
    CALLED ON NULL INPUT
    SPECIFIC LAMONTRE1/READRSP1C
    ALLOW DEBUG MODE
    FENCED
    BEGIN
    DECLARE C1 CURSOR FOR SELECT * FROM QIWS/QCUSTCDT;
    OPEN C1;
    RETURN;
    END

PLSQL, dynamic Statement

--  DROP PROCEDURE  LAMONTRE1.READRSP2    ;
    CREATE PROCEDURE  LAMONTRE1.READRSP2
    LANGUAGE SQL
    SPECIFIC LAMONTRE1.READRSP2
    DYNAMIC RESULT SET 1
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    BEGIN
    DECLARE MYSFW VARCHAR(500);
    DECLARE K1 CURSOR FOR C1;
    SET MYSFW ='SELECT * FROM QIWS.QCUSTCDT'   ;
    PREPARE C1 FROM MYSFW;
    OPEN K1;
    RETURN;
    END;
    ;
-- RUN THE DEMO :
--  CALL LAMONTRE1.READRSP2()

I've not try to change the list of selected columns, but I will test that soon

I'm more fluent in IBMi than in english. So, don't hesitate to send me back any page or excerpt that may need a correction.