References & SQL Tips Speaker: David Larsen ( - PDF document

References & SQL Tips Speaker: David Larsen ( dlarsen@lagoonpark.com) , Software Engineer at Lagoon Corporation Topic: SQL Tips and Techniques on the IBM i Date: Wednesday, January 14th, 2015 Time: 11:00 a.m. – 12:00 noon Location: M urray City Offices (City Council Chambers), 5025 South State Street, M urray, Utah. Modernizing IBM i Applications from the Database up to the User Interface and Everything in Between sg248185.pdf Read and Study Chapters 8 and 9. RCAC for DB2 for i http://www.redbooks.ibm.com/redpieces/pdfs/redp5110.pdf IBM i Version 7.2 Database DB2 for i SQL Reference - 1,596 pages http://www-01.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/db2/rbafzpdf.pdf IBM i Version 7.2 Database SQL programming http://www-01.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/sqlp/rbafypdf.pdf http://iprodeveloper.com/database/teach-your-old-db2-new-tricks http://iprodeveloper.com/sql/simplify-dds-sql-conversion Replacing a DDS Physical File with an SQL Table http://iprodeveloper.com/database/replacing-dds-physical-file-sql-table Database Harmony: "Traditional" and SQL Coexistence http:/ /iprodeveloper.com/ rpg-programming/ database-harmony-traditional-and-sql-coexistence More SQL and dates part I http://www.rpgpgm.com/2013/09/more-sql-and-dates-part-i.html http://www.rpgpgm.com/2013/09/more-sql-and-dates-part-ii.html http://www.rpgpgm.com/2014/09/ftp-i-to-i-part-3.html Using Date Tools for SQL and Excel http://iprodeveloper.com/database/using-date-tools-sql-and-excel Get Your SQL Surge On http://event.on24.com/eventRegistration/EventLobbyServlet?target=lobby.jsp&eventid=835928&sessionid=1&partnerr ef=thankyous&key=6AA27A32AF883CFE9D34923719F7148F&eventuserid=103379436 President – Gary Larson Vice President – Matt Kingdon Utah IBM i Professionals Association – 2014-2015 Secretary – Joe Wood (P.O. Box 571394 – Murray, UT 84157) Treasurer – Mike Meservy http://www.uiipa.org

Procedures, and Functions, and Catalogs! Oh My! http://www.mcpressonline.com/programming/sql/procedures-and-functions-and-catalogs-oh-my.html In 7.1 SQLRPGLE Can Read Stored Procedure Result Sets From <http://iprodeveloper.com/sql/71-sqlrpgle-can-read-stored-procedure-result-sets> IBM DB2 for i Advanced SQL Programming - IBM Training From <http://www.youtube.com/watch?v=aBzZXMrXVGY> Coding SQL Routines on the Fast Track From <http://iprodeveloper.com/database/coding-sql-routines-fast-track> SQL's Other Fetch Options From <http://www.itjungle.com/fhg/fhg051309-story02.html> Good stuff! Set eof or bof. Sequential and random reads Creating a SQL table "on the fly" From <http://www.rpgpgm.com/2014/01/creating-sql-table-on-fly.html> Resetting the identity column in a SQL table http://www.rpgpgm.com/2014/06/resetting-identity-column-in-sql-table.html Complex Joins in SQL http://www.toadworld.com/platforms/ibmdb2/w/wiki/7755.complex-sql-guidelines.aspx Embedding SQL in RPG IV--Episode II http://www.itjungle.com/fhg/fhg082405-story01.html SQL Functions Extend RPG http://iprodeveloper.com/rpg-programming/sql-functions-extend-rpg COMPARE TWO TABLES and get only unmatched records To compare all the columns/rows in TABLE1 to those in TABLE2 you can use the EXCEPT function as follows: SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2; The above SQL will display all rows from TABLE1 that don’t match or exist on TABLE2. The number of columns being compared – and their data type – have to match and you can’t compare certain column types (such as CLOB/BLOB/XML). The nice thing about this, is the EXCEPT function takes care of all the comparison details for you unlike when trying to use the WHERE NOT EXISTS clause. From <http://jmerrell.com/2011/06/01/db2-except-sql-function/> Create Database Files from SQL Queries From <http://www.itjungle.com/fhg/fhg111506-story01.html> REPLACE to Replace Characters in a String From <http://www.itjungle.com/fhg/fhg072711-story01.html> SELECT name, company, REPLACE(phone, '-', '') AS phone FROM namemstr Create or Replace a File President – Gary Larson Vice President – Matt Kingdon Utah IBM i Professionals Association – 2014-2015 Secretary – Joe Wood (P.O. Box 571394 – Murray, UT 84157) Treasurer – Mike Meservy http://www.uiipa.org

http://iprodeveloper.com/database/use-sql-create-or-replace-improve-db2-i-object-management A Match Made in SQL From <http://iprodeveloper.com/sql/match-made-sql> Embedding SQL in RPGLE http://www.penton.com/webcasts/Day2Session3-IntrotoEmbeddedSQL_Cruikshank.pdf How to use DB2's cross-reference files to help http://search400.techtarget.com/tip/How-to-use-DB2s-cross-reference-files-to-help-manage-your-database-Part-I> SQL Arrays in DB2 for i 7.1 http://ibmsystemsmag.com/ibmi/administrator/db2/SQL-Arrays-in-DB2-for-i-7-1/ Free Format SQLRPGLE Shell Program 1 Author: Lionel Halvorsen http://www.ilerpgprogramming.com/2010/02/free-format-sqlrpgle-shell-program-1.html Free Format SQLRPGLE Shell Program: part 2 Author: Lionel Halvorsen Feb 26, 2010 <http://www.ilerpgprogramming.com/2010/02/free-format-sqlrpgle-shell-program-part.html> Free Format SQLRPGLE Shell Program: part 3 Author: Lionel Halvorsen Mar 1, 2010 From <http://www.ilerpgprogramming.com/2010/03/free-format-sqlrpgle-shell-program-part.html> Comparing SQL DDL with DDS for creating files. From <http://www.ilerpgprogramming.com/2009/05/comparing-sql-ddl-with-dds-for-creating.html> SQL in free format RPG Do you use SQL and free format RPG independently but haven't really put the two together yet? If so then this article is for you. Executing SQL statements in free format RPG may be a lot easier than you think. Doing so can streamline and speed up your legacy code. From <http://www.ilerpgprogramming.com/2009/04/sql-in-free-format-rpg.html> Run SQL statements automatically with RUNSQLSTM STRSQL is a powerful and useful interactive tool (in the right hands) but it can be cumbersome to retrieve previous SQL statements, modify them and then run them again all the while hoping there are no typos. If you find yourself frequently entering the same SQL commands why not save them to a text file and automate the process? I like to save the SQL statements in a source file named qtxtsrc . Once saved the SQL can be executed from a CL containing the RUNSQLSTM command. In this example two inventory file field definitions were reversed during an upgrade and the data had to be swapped (mapped correctly) during the process. This can be accomplished with one SQL statement. Note that there is no need for messy work fields that might otherwise be used when coding the same solution in RPG. Step 1. Create the text type source member. Contents of the source member sqlfswap (saved as a text file): update datalib/invenpf set field1 = field2, field2 = field1 President – Gary Larson Vice President – Matt Kingdon Utah IBM i Professionals Association – 2014-2015 Secretary – Joe Wood (P.O. Box 571394 – Murray, UT 84157) Treasurer – Mike Meservy http://www.uiipa.org