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

President – Gary Larson Vice President – Matt Kingdon Secretary – Joe Wood Treasurer – Mike Meservy

Utah IBM i Professionals Association – 2014-2015

(P.O. Box 571394 – Murray, UT 84157) http://www.uiipa.org

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 Secretary – Joe Wood Treasurer – Mike Meservy

Utah IBM i Professionals Association – 2014-2015

(P.O. Box 571394 – Murray, UT 84157) 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 Secretary – Joe Wood Treasurer – Mike Meservy

Utah IBM i Professionals Association – 2014-2015

(P.O. Box 571394 – Murray, UT 84157) 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 Secretary – Joe Wood Treasurer – Mike Meservy

Utah IBM i Professionals Association – 2014-2015

(P.O. Box 571394 – Murray, UT 84157) http://www.uiipa.org

Step 2. Create CL member sqlfswapcl: ADDLIBLE mylib MONMSG msgid(cpf0000) RUNSQLSTM srcfile(mylib/qtxtsrc) srcmbr(sqlfswap) MONMSG msgid(cpf0000) endpgm Step 3. Run the CL and check your results. Of course the CL can be set up on the job scheduler (wrkjobscde) if your application needs to be run automatically at specified intervals and multiple sql statements can be added into the same text source member. From <http://www.ilerpgprogramming.com/2009/02/run-sql-statements-automatically-with.html> Yet another reason why to work with real date and time values: SQL provides a lot of scalar functions for date and time calculation that can be easily used in RPG. Example: /Free Exec SQL Set :MyWeekIso = Week_ISO(:MyDate); Dsply MyWeekIso; Exec SQL Set :MyMonthEnd = Last_Day(:MyDate); Exec SQL Set :NextMonday = Next_Day(:MyDate, 'Monday'); Exec SQL Set :IsWeekEnd = Case When DayOfWeek_ISO >= 6 Then '1' Else '0' End; /End-Free Those functions only work with real date/time fields. SQL does not play very nicely with numeric date/time fields. From <http://ibmsystemsmag.blogs.com/idevelop/2014/07/rpg-speed-dating-.html> iProdeveloper – PowerPack – Turbocharge IBM i with SQL. http://iprodeveloper.com/power-packs/turbocharge-ibm-i-sql-power-pack

Simplify DDS to SQL Conversion

TR7 adds support for keyed file objects to the Generate SQL utility http://iprodeveloper.com/sql/simplify-dds-sql-conversion Achieve improved database interoperability with SQL and RDB aliases http://www.ibm.com/developerworks/ibmi/library/i-improved-database-interoperability-sql- rdb/index.html?ca=drs- Selected common SQL features for developers of portable DB2 applications http://www.ibm.com/developerworks/data/library/techarticle/db2common/previouslevel3.html?ca=drs- TechTip: Direct SQL Control of System Names http://www.mcpressonline.com/sql/techtip-direct-sql-control-of-system-names.html Coding SQL statements in ILE RPG applications http://www-01.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/rzajp/rzajprpgi.htm You can generate the SQL to create a collection (library) and all physical and logical indexes in the selected library needed with System I Navigator. Take the option to create the output to a source file member. Then use RUNSQLSTM to execute the statements from that member to create your new collection. ATTENTION: You will need to put the correct right margin length on RUNSQLSTM or it will not work (EG default = MARGINS(80); otherwise = MARGINS(100)

President – Gary Larson Vice President – Matt Kingdon Secretary – Joe Wood Treasurer – Mike Meservy

Utah IBM i Professionals Association – 2014-2015

(P.O. Box 571394 – Murray, UT 84157) http://www.uiipa.org

Cast Your Data Conversion Troubles Away

If you have different CCSIDs for tables or columns on your system http://www.itjungle.com/mpo/mpo091103-story03.html

SQL Functions You Didn't Know You Had, Part 1

http://www.itjungle.com/fhg/fhg102214-story01.html

SQL Functions You Didn't Know You Had, Part 2

http:/ /www.itjungle.com/ fhg/fhg111214-story02.html>

IBM I Links you will need

http://ibmsystemsmag.com/Blogs/You-and-i/December-2014/IBM-i-Links-You-Need/?utm_campaign=ibm- enews&utm_medium=email&utm_source=ibmi-jan14-2015-newver&utm_content=blog-youandi-title

Paging Cursors

http://www.itjungle.com/fhg/fhg102214-story02.html

SQL UNION to get subtotals

http:/ / search400.techtarget.com/ tip/ SQL-UNION-to-get-subtotals SELECT Company, Region, District, ' ', COUNT(* ) FROM StoreTbl GROUP BY Company, Region, District UNION SELECT Company, Region, 999, 'Region ' | | DIGITS(Region), COUNT(* ) FROM StoreTbl GROUP BY Company, Region UNION SELECT Company, 999 999, 'Company' | | Company, count(* ) from StoreTbl group by Company

• rder by Company

IBM DB2 for i: Tips Frequently asked questions: Improving performance with SQL packages

http://www-03.ibm.com/systems/power/software/i/db2/support/tips/sqlperffaq.html#header_1

A Concise Guide to SQL Triggers

http:/ /iprodeveloper.com/ database/concise-guide-sql-triggers

President – Gary Larson Vice President – Matt Kingdon Secretary – Joe Wood Treasurer – Mike Meservy

Utah IBM i Professionals Association – 2014-2015

(P.O. Box 571394 – Murray, UT 84157) http://www.uiipa.org

Education

www.w3schools.com www.codeacademy.com www.udemy.com www.edx.com www.php.net www.youtube.com

References

Database Embedded Programming http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/ssw_ibm_i_72/rzajp/rzajppdf.pdf

DDS and SQL - The Winning Combination for DB2 for i: White Paper Download Link

http://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/whitepaper/ibmi/db2/sql IBM i Database SQL Programming 7.1 rbafy http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/ssw_ibm_i_71/sqlp/rbafy.pdf DB2 for I SQL Reference Manual http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzpdf.pdf?lang=en-us Improving SQL procedure performance by Kent Milligan http://www- 304.ibm.com/partnerworld/wps/servlet/ContentHandler/whitepaper/ibmi/sql_sql_v6r1/procedure Moving From OPNQRYF to SQL http://www- 304.ibm.com/partnerworld/wps/servlet/ContentHandler/whitepaper/i5os/OPNQRYF_SQL/move?pageI d=pw.sellingresources Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries http://www.redbooks.ibm.com/redbooks.nsf/RedbookAbstracts/sg246503.html?Open Using SQL views and stored procedures with DB2 Web Query http://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/whitepaper/i5os/db2_sql/security