Using SQL table function to lookup SQL codes

It has always been a bit of a bind to look up what a SQL codes means, to be able to discover what error or warning Db2 for i is giving me for a SQL statement I have executed. There is a message file, QSQLMSG, where I can convert the SQL code into a message id and then look in the message file for the message texts:

SQL code -501 = Message id SQL0501 DSPMSGD RANGE(SQL0501) MSGF(QSQLMSG)

With the technology refreshes for IBM i 7.5 TR3 and 7.4 TR9 comes a table function where I can pass it the SQL code, and the results include all the information I desire:

• Message id
• Message text
• Second level message text

All I need to do is to pass the SQL code to it for example if I want to know what the message texts for SQL codes 100 and -501 are I can just:

01 SELECT * FROM TABLE(SYSTOOLS.SQLCODE_INFO(100)) 02 UNION 03 SELECT * FROM TABLE(SYSTOOLS.SQLCODE_INFO(-501))

Lines 1 and 3: The statement is very simple I just have the table function followed by the SQL code in parentheses ( ( ) ).

Line 2: As I want to combine the results from both statements into one result I use the Union clause.

Before I forget to remind you all: a positive SQL code is a warning, and a negative one is an error.

The returned result is:

MESSAGE _ID MESSAGE_TEXT MESSAGE_SECOND_LEVEL_TEXT ------- --------------------- -------------------------------------------------- SQL0100 Row not found for amp;1. Cause . . . . . : One of the following con... SQL0501 Cursor &1 not open. Cause . . . . . : The cursor &1 was specified...

If I pass a value that is not a valid SQL code, no result is returned:

SELECT * FROM TABLE(SYSTOOLS.SQLCODE_INFO(999)) MESSAGE _ID MESSAGE_TEXT MESSAGE_SECOND_LEVEL_TEXT ------- --------------------- --------------------------------------------------

I am not going to want to type in this whole statement every time I want to know what the description of a SQL code is. I created for a program with a display file. I enter the SQL code and all the three columns of the results are presented upon a screen.

The example I am showing is a simplified version of what I created for myself. Here I have called the RPG program FNDSQLCD and the display file FNDSQLCDED.

Let me start by showing the code for the display file:

01 A DSPSIZ(24 80 *DS3) 02 A CA03(03 'Exit') 03 A INDARA 04 A R SCREEN 05 A 2 3'Enter SQL code . . . . . :' 06 A ZSQLCODE 6A B 2 30DSPATR(HI) 07 A 4 3'Message id . . . . . . . :' 08 A ZMSGID 7A O 4 30 09 A 5 3'Message text . . . . . . :' 10 A ZMSGTXT 132A O 5 30 11 A 8 3'Message second level text:' 12 A ZMSGTXT2 1000A O 9 3

Line 2: Even though I do not show that the F3 key is for exit on the screen, the program will exit if F3 is pressed.

Line 3: In 2024 there is no excuse to use the *INKC or *IN03 in the RPG program. By using the indicator area/data structure I can give a real descriptive name to the indicator in the RPG program.

Line 6: This is the only input field, for the SQL code. I made it character to make it easier to enter a negative as '-501', rather than have a numeric field and press the Field Minus key to make it negative.

Line 8: The IBM i message id of the SQL code.

Line 10: I have included the entire message text, knowing it will wrap to three lines.

Line 12: The second level message text is three thousand characters long, I am only going to display the first thousand.

When I look at the screen in SDA...

STRSDA OPTION(3) SRCFILE(*LIBL/DEVSRC) SRCMBR(FNDSQLCDED)

I can see its layout:

Enter SQL code . . . . . : BBBBBB Message id . . . . . . . : OOOOOOO Message text . . . . . . : OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO O Message second level text: OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO

This is the source for the RPG program:

01 **free 02 ctl-opt main(Main) dftactgrp(*no) ; 03 dcl-f FNDSQLCDED workstn indds(Dspf) ; 04 dcl-ds Dspf qualified ; 05 Exit ind pos(3) ; 07 end-ds ; 08 dcl-ds Data ; 09 ZMSGID char(7) ; 10 ZMSGTXT char(132) ; 11 ZMSGTXT2 char(1000) ; 12 end-ds ; 13 dcl-proc Main ; 14 dow (*on) ; 15 exfmt SCREEN ; 16 if (Dspf.Exit) ; 17 leave ; 18 endif ; 19 clear Data ; 20 if (ZSQLCODE = ' ') ; 21 iter ; 22 endif ; 23 exec sql SELECT * INTO :Data 24 FROM TABLE(SYSTOOLS.SQLCODE_INFO(:ZSQLCODE)) ; 25 enddo ; 26 return ; 27 end-proc ;

Line 1: In 2024 all new code should be totally free RPG.

Line 2: I am using a Main procedure in this program, therefore, I need the Main control option.

Line 3: The definition of the display file.

Lines 4 7: The indicator data structure, the only function key that is available is F3.

Lines 8 12: This data structure will be used to receive the results from the SQLCIODE_INFO table function.

Line 13: The start of the Main procedure.

Lines 14 25: A Do loop to display the screen from the display file and get the information about the SQL code.

Lines 20 - 22: If no SQL code was entered do not bother to execute the SQL statement to get the SQL code's information. Just iterate (loop).

Lines 23: As I am only getting one row of results I do not have to use a SQL cursor. I can just use a SQL Select statement and retrieve the returned row into the data structure defined on lines 8 12.

Line 25: As this is the end of the Do loop the logic returns to line 14, and when the record format is displayed it will contain the information for the entered SQL code.

Below are some examples of the screen for several SQL codes. First for the -501 error:

Enter SQL code . . . . . : -501 Message id . . . . . . . : SQL0501 Message text . . . . . . : Cursor &1 not open. Message second level text: Cause . . . . . : The cursor &1 was specified in a FETCH or CLOSE statement, but the cursor is not open. Cursor &1 has one of the following conditions: -- C ursor &1 was never opened. -- The cursor &1 was opened in another program or ano ther call of this program and the program was created with CLOSQLCSR(*ENDPGM). - - The cursor &1 was opened in another module or another call of this module and the module was created with CLOSQLCSR(*ENDMOD). -- The cursor &1 was opened in a nother call of this program and programs which have run SQL statements have ende d and the program was created with CLOSQLCSR(*ENDSQL). -- The cursor &1 was open ed in another call of this module and the activation group ended between calls. The module was created with CLOSQLCSR(*ENDACTGRP). -- The cursor was closed by a CLOSE, COMMIT, or ROLLBACK statement. -- The cursor &1 was opened under a trans action which is different than the current transaction. Recovery . . . : Do o ne of the following and precompile again:

Now for the SQL code 100, which is a warning:

Enter SQL code . . . . . : 100 Message id . . . . . . . : SQL0100 Message text . . . . . . : Row not found for &1. Message second level text: Cause . . . . . : One of the following conditions has occurred: -- If this i s a FETCH statement, no more rows satisfy the selection values (end of file). T he name of the cursor is &1 and the result set identifier is &2. If the result s et identifier is non-zero, the result table for this cursor was being accessed a s a stored procedure result set. -- If this is a FETCH statement for a scrollabl e cursor, a record was not found. If NEXT was specified, end of file was reache d. If PRIOR was specified, the beginning of the file was reached. If RELATIVE was specified, either the beginning of file or the end of file was reached, depe nding on the value specified. If FIRST or LAST was specified, then no records s atisfy the selection criteria. The name of the cursor is &1. -- If this is an em bedded SELECT statement, no rows satisfy the selection values. -- If this is an UPDATE, INSERT, DELETE, or MERGE statement, no rows satisfy the subselect, WHERE clause, or MERGE criteria. No rows were

Finally this is what is displayed if the SQL code is not valid:

Enter SQL code . . . . . : 0 Message id . . . . . . . : Message text . . . . . . : Message second level text:

When I showed this to my colleagues one mentioned that he could use SQLCODE_INFO to get a description for any error his program may encounter. If you want to do this do not use this table function, use GET DIAGNOSTICS instead.

 

You can learn more about the SQLCODE_INFO SQL table function from the IBM website here.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.