SQL Tips & Techniques April DVCUG Meeting

SQL Tips & TechniquesApril DVCUG Meeting Fred Gamache Gamache Consulting

SQL Products • SQL Developers Kit • Ability to embed SQL in RPG, etc. • STRSQL command to run interactive SQL • RUNSQLSTM – runs a statement from a source file • Client Access • iSeries Navigator – Databases, SQL script • ODBC/OLE DB – from Access, Excel, etc. • Query Manager © 2003 Gamache Consulting

Query Manager • Create an object from a source file member • Source can contain any valid SQL statement • Compilation and Execution are separate steps – just like a program © 2003 Gamache Consulting

Query Manager – Create QM Query Create Query Management Query (CRTQMQRY) Type choices, press Enter. Query management query . . . . . Name Library . . . . . . . . . . . *CURLIB Name, *CURLIB Source file . . . . . . . . . . QQMQRYSRC Name Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB Source member . . . . . . . . . *QMQRY Name, *QMQRY Text 'description' . . . . . . . *SRCMBRTXT Sort sequence . . . . . . . . . *SRC Name, *SRC, *JOBRUN, *JOB... Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB Language ID . . . . . . . . . . *SRC *JOB, *JOBRUN... Additional Parameters Authority . . . . . . . . . . . *LIBCRTAUT Name, *USE, *CHANGE, *ALL... Replace object . . . . . . . . . *YES *YES, *NO © 2003 Gamache Consulting

Query Manager – Start QM Query Start Query Management Query (STRQMQRY) Type choices, press Enter. Query management query . . . . . Name Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB Output . . . . . . . . . . . . . * *, *PRINT, *OUTFILE Query management report form . . *SYSDFT Name, *SYSDFT, *QMQRY Library . . . . . . . . . . . Name, *LIBL, *CURLIB Additional Parameters Relational database . . . . . . *NONE Connection Method . . . . . . . *DUW *DUW, *RUW User . . . . . . . . . . . . . . *CURRENT Name, *CURRENT Password . . . . . . . . . . . . Character value, *NONE Naming convention . . . . . . . *SYS *SYS, *SQL, *SAA Allow information from QRYDFN . *NO *NO, *YES, *ONLY Set variables: Variable name . . . . . . . . Variable value . . . . . . . . © 2003 Gamache Consulting

Prompted QM Queries • Gives you the ability to create an object that will prompt you at run time to fill in the parameter • Interactively you get prompted • In a CL program you can set a variable in your program © 2003 Gamache Consulting

Prompted QM Queries - Examples Select * from ordhead where ordstat = ‘O’and custid = &CUSTOMER Select * from custmast where cusnam like &CUSNAME You need to put the quotes into the value.When prompted, you’d enter: ‘DVCU%’ © 2003 Gamache Consulting

Query Manager • Place a SQL statement in a source file like QQMQRYSRC (use length of 91) • CRTQMQRY – to create a Query Manager Query • STRQMQRY – to run a Query Manager Query • PDM Options – QC - CRTQMQRY QMQRY(&L/&N) SRCFILE(&L/&F) SRCMBR(&N) QR - STRQMQRY QMQRY(&L/&N) © 2003 Gamache Consulting

Retrieve SQL from Query/400 • The Retrieve QM Query command will retrieve the SQL source from a Query/400 object • Puts the SQL used in a Query/400 query into a source file so you can then view it, modify it, and create a QM Query from it. • Can also use this to replace a static Query with a prompted Query Manager query © 2003 Gamache Consulting

Retrieve QM Query Command Retrieve Query Mgmt Query (RTVQMQRY) Type choices, press Enter. Query management query . . . . . Name Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB Source file . . . . . . . . . . Name Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB Source member . . . . . . . . . *QMQRY Name, *QMQRY Allow information from QRYDFN . *NO *NO, *YES, *ONLY © 2003 Gamache Consulting

How to find Queries that use a file PGM PARM(&LIBRARY &FILE) DCL VAR(&LIBRARY) TYPE(*CHAR) LEN(10) DCL VAR(&FILE) TYPE(*CHAR) LEN(10) DCLF FILE(QTEMP/QRYOBJS) DLTF FILE(QTEMP/QRYOBJS) MONMSG CPF0000 DLTF FILE(QTEMP/&LIBRARY) MONMSG CPF0000 CRTSRCPF FILE(QTEMP/&LIBRARY) DSPOBJD OBJ(&LIBRARY/*ALL) OBJTYPE(*QRYDFN) + DETAIL(*FULL) OUTPUT(*OUTFILE) + OUTFILE(QTEMP/QRYOBJS) BEGIN: RCVF /* GET QUERY NAME AND LIBRARY NAME */ /* IF END OF FILE REACHED, EXIT LOOP */ MONMSG CPF0864 EXEC(GOTO EOF) RTVQMQRY QMQRY(&ODLBNM/&ODOBNM) + SRCFILE(QTEMP/&LIBRARY) ALWQRYDFN(*ONLY) GOTO CMDLBL(BEGIN) EOF: FNDSTRPDM STRING(&FILE) FILE(QTEMP/&LIBRARY) MBR(*ALL) OPTION(*NONE) PRTMBRLIST(*YES) ENDPGM © 2003 Gamache Consulting

The UNION Statement • This takes the results of two SQL statements and combines the output, in effect merging the two result sets • The fields in both statements must have the same attributes (char, decimal, etc.) • Must have the same number of fields in both statements © 2003 Gamache Consulting

The Union Statement - Example Select ‘Order’ as rowtype, order, price, extprice From custord Union Select ‘Total’ as rowtype, 0 as order, 0, sum(extprice) From custord Order by rowtype, order © 2003 Gamache Consulting

Alter Table • Use Alter Table to add, delete or change a column in a table. • You do NOT need to manually save the data, it does it for you • Example: Alter Table Customer Add contact char(30) • If you add a column with “Not Null”, you must specify a default value © 2003 Gamache Consulting

Alter Table • If you Drop a column you receive a CPF warning that you must answerChange of file TEST1 may cause data to be lost. (C I) • This can be a problem if you’re running the Alter Table in a client server application, through ODBC for example. © 2003 Gamache Consulting

Alter Table – Drop Field • You need to manipulate the system reply list to answer that message call qsys.qcmdexc( 'ADDRPYLE SEQNBR(3333) MSGID(CPA32B2) RPY(''I'')',0000000045.00000) CALL QSYS.QCMDEXC('QSYS/CHGJOB INQMSGRPY(*SYSRPYL)',0000000031.00000) Alter table x drop column A call qsys.qcmdexc( 'RMVRPYLE SEQNBR(3333)',0000000021.00000) CALL QSYS.QCMDEXC('QSYS/CHGJOB INQMSGRPY(*DFT)',0000000027.00000) © 2003 Gamache Consulting

SQL Tips & Techniques April DVCUG Meeting