Mike
Reputation: 1324
Is this the correct behavior for SQL GET DIAGNOSTICS?
The documentation for the SQL statement GET DIAGNOSTICS indicates that you can request that diagnostic data be placed in specific variables, with the relevant variable names and types listed. The articles here and here show how one can use this SQL statement in the context of an RPG program.
It looks like some of the results that I am getting back from DB2 do not match what the documentation and articles say. I am on IBM i, version 7.3.
I am looking at the diagnostic data that is being returned from a SQL user-defined table function (UDTF). If you use the clause "ALL" you can get a string that contains all the values:
COMMAND_FUNCTION=FETCH;
COMMAND_FUNCTION_CODE=+45;
DB2_NUMBER_CONNECTIONS=+1;
MORE=N;
NUMBER=+1;
CONDITION_NUMBER=+1;
DB2_MESSAGE_ID=SQL0443;
DB2_MESSAGE_ID1=CPF503E;
DB2_MODULE_DETECTING_ERROR=QSQFETCH;
DB2_ORDINAL_TOKEN_1=*N.*N;
DB2_ORDINAL_TOKEN_2=DUMMYUDTF;
DB2_ORDINAL_TOKEN_3=*******.DUMMYUDTF;
DB2_ORDINAL_TOKEN_4=Parameter '2' cannot be null.;
DB2_RETURNED_SQLCODE=-443;
DB2_TOKEN_COUNT=+4;
DB2_TOKEN_STRING=*N.*NDUMMYUDTFDUMMYUDTFParameter '2' cannot be null.;
MESSAGE_LENGTH=Parameter '2' cannot be null.;
MESSAGE_OCTET_LENGTH=Parameter '2' cannot be null.;
MESSAGE_TEXT=Parameter '2' cannot be null.;
RETURNED_SQLSTATE=90002;
ROUTINE_NAME=DUMMYUDTF;
ROUTINE_SCHEMA=QGPL;
SERVER_NAME=******;
SPECIFIC_NAME=DUMMYUDTF;
DB2_SQLERRD_SET=Y;
DB2_SQLERRD1=-168758331;
CONNECTION_NAME=******;
DB2_AUTHORIZATION_ID=********;
DB2_CONNECTION_METHOD=D;
DB2_CONNECTION_NUMBER=+1;
DB2_CONNECTION_STATE=+1;
DB2_CONNECTION_TYPE=+1;
DB2_PRODUCT_ID=QSQ07030;
DB2_SERVER_CLASS_NAME=DB2 for i;
DB2_SERVER_NAME=******;
The documentation states that fields MESSAGE_LENGTH and MESSAGE_OCTET_LENGTH should be integers. As you can see they are both VARCHARs. If I write code that defines these variables as integers, I get escape message: Scalar operand does not have attributes required by instruction. in the job log and two diagnostic, severity 50 SQL system error. along with a SQL service dump QSQSVCDMP. Another anomoly is that variable DB2_MESSAGE_ID2 does not look like it is implemented and can make the program crash on the line where SQL assigns it.
In contrast, if I use GET DIAGNOSTICS on a built-in function, the data types all match what the documentation says and all the variables seem like they are available:
COMMAND_FUNCTION=SELECT;
COMMAND_FUNCTION_CODE=+65;
DB2_NUMBER_CONNECTIONS=+1;
MORE=N;
NUMBER=+1;
CLASS_ORIGIN=ISO9075;
CONDITION_NUMBER=+1;
DB2_MESSAGE_ID=SQL0138;
DB2_MESSAGE_ID1=CPF4278;
DB2_MESSAGE_ID2=CPD4317;
DB2_MODULE_DETECTING_ERROR=QSQOPEN;
DB2_ORDINAL_TOKEN_1=*N;
DB2_RETURNED_SQLCODE=-138;
DB2_TOKEN_COUNT=+1;
DB2_TOKEN_STRING=*N;
MESSAGE_LENGTH=+47;
MESSAGE_OCTET_LENGTH=+47;
MESSAGE_TEXT=Argument *N of substringing function not valid.;
RETURNED_SQLSTATE=22011;
ROUTINE_CATALOG=******;
SERVER_NAME=******;
SUBCLASS_ORIGIN=ISO9075;
DB2_SQLERRD_SET=Y;
DB2_SQLERRD1=-185403400;
DB2_SQLERRD2=-185339401;
CONNECTION_NAME=******;
DB2_AUTHORIZATION_ID=********;
DB2_CONNECTION_METHOD=D;
DB2_CONNECTION_NUMBER=+1;
DB2_CONNECTION_STATE=+1;
DB2_CONNECTION_TYPE=+1;
DB2_PRODUCT_ID=QSQ07030;
DB2_SERVER_CLASS_NAME=DB2 for i;
DB2_SERVER_NAME=******;
Is this a bug that should be reported to IBM? How does one go about doing that? I have made a UDTF and a calling RPGLE program to demonstrate the problem. That code is below if anyone wants to verify my findings. You will probably have to comment out a few lines in RPGLE program TSTDIAUDTF because you do not have my error message service program ERRFUNC.
DUMMYUDTF is an external user-defined SQL function with code in SQL and in RPGLE. TSTDIAUDTF is an RPGLE program that I am using to test the behavior of the GET DIAGNOSTICS SQL statement.
This seems broadly similar to some bugs reported in the past, for instance SI44066, but the details are different.
Edit: Corrected code formatting errors from earlier botched paste operation.
DUMMYUDTF, TXT
-- Build function by calling:
-- RUNSQLSTM SRCFILE(*******/QTXTSRC) SRCMBR(DUMMYUDTF) COMMIT(*NONE)
CREATE OR REPLACE FUNCTION DummyUDTF(
parm1 NUMERIC(7,0),
parm2 VARCHAR(7)
parm3 VARCHAR(4))
RETURNS TABLE (Out1 NUMERIC(7,0),
Out2 VARCHAR(13),
Out3 VARCHAR(4),
Out4 NUMERIC(7,2),
Out5 NUMERIC(5,4))
LANGUAGE RPGLE
DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
DISALLOW PARALLEL
NOT FENCED
EXTERNAL NAME '******/DUMMYUDTF'
PARAMETER STYLE DB2SQL;
DUMMYUDTF, SQLRPGLE
/IF DEFINED (*CRTBNDRPG)
H DFTACTGRP(*NO) ACTGRP('DUMMYUDTF')
/ENDIF
H DEBUG
H ALWNULL(*USRCTL) OPTION(*NoDebugIO) EXTBININT(*YES)
D DUMMYUDTF PI
* Input Parameters
D parm1 7S 0
D parm2 7A VARYING
D parm3 4A VARYING
* Columns to be returned
D out1 7S 0
D out2 13A VARYING
D out3 4A VARYING
D out4 7S 2
D out5 5S 4
* NULL Indicators for all input and output parameters
D parm1_n 5I 0
D parm2_n 5I 0
D parm3_n 5I 0
D out1_n 5I 0
D out2_n 5I 0
D out3_n 5I 0
D out4_n 5I 0
D out5_n 5I 0
* SQL Function Parameters
D OutputSQLState 5A
D FunctionName 517A VARYING CONST
D SpecificName 128A VARYING CONST
D MessageText 1000A VARYING
D CallType 10I 0
* Locals
D CallTypeFirst C CONST(-2)
D CallTypeOpen C CONST(-1)
D CallTypeFetch C CONST(0)
D CallTypeClose C CONST(1)
D CallTypeFinal C CONST(2)
D out DS TEMPLATE QUALIFIED
D out1 7S 0
D out2 13A VARYING
D out3 4A VARYING
D out4 7S 2
D out5 5S 4
D results DS DIM(99) LIKEDS(out)
D index S 2S 0
D count S 2S 0
D var4 S 7S 2
D var5 S 5S 4
MONITOR;
OutputSQLState = *ZEROS; // Initialize Output Parameters
CLEAR MessageText;
SELECT;
WHEN CallType = CallTypeOpen;
IF parm1_n = -1;
MessageText = BuildNullError('1');
RETURN;
ENDIF;
IF parm2_n = -1;
MessageText = BuildNullError('2');
RETURN;
ENDIF;
IF parm3_n = -1;
MessageText = BuildNullError('3');
RETURN;
ENDIF;
IF parm1 < 1;
OutputSQLState = '90002';
MessageText = 'Parameter 1 must be a number greater than 0.';
RETURN;
ENDIF;
EXSR BuildDummyData;
WHEN CallType = CallTypeFetch;
index += 1;
IF index > count;
OutputSQLState = '02000';
RETURN;
ENDIF;
out1 = results(index).out1;
out2 = results(index).out2;
out3 = results(index).out3;
out4 = results(index).out4;
out5 = results(index).out5;
WHEN CallType = CallTypeClose;
*INLR = *ON;
ENDSL;
ON-ERROR;
DUMP; // This dump ends up in the output queue QUSRSYS\QEZDEBUG
OutputSQLState = '90001';
MessageText = 'Error occurred in RPG program ''DUMMYUDTF''. ' +
'See program dump for additional details.';
ENDMON;
RETURN;
********************************************************************
BEGSR BuildDummyData;
EXEC SQL SELECT 350.05, .65
INTO :var4, :var5
FROM SYSIBM.SYSDUMMY1;
SaveToResults(parm1 : parm2 : parm3 : var4 : var5);
SaveToResults(219 : 'ABCDEF' : 'ZYXW' : 20.1 : .65);
SaveToResults(438 : 'GHIJKLMNOP' : 'ZYXW' : 2.95 : 0);
ENDSR;
********************************************************************
P BuildNullError...
P B
D PI 1000A VARYING
D parmName 20A VALUE VARYING
OutputSQLState = '90002';
RETURN 'Parameter ''' + parmName + ''' cannot be null.';
P E
********************************************************************
P SaveToResults...
P B
D PI
D loc1 7S 0 VALUE
D loc2 13A VALUE VARYING
D loc3 4A VALUE VARYING
D loc4 7S 2 VALUE
D loc5 5S 4 VALUE
IF count = 99;
OutputSQLState = '10Z01';
MessageText = 'SQL warning in DUMMYUDTF +
More than 99 parts generated.';
ELSE;
count += 1;
results(count).out1 = loc1;
results(count).out2 = loc2;
results(count).out3 = loc3;
results(count).out4 = loc4;
results(count).out5 = loc5;
ENDIF;
P E
TSTDIAUDTF, SQLRPGLE
H DEBUG(*YES)
H MAIN(Main)
H DFTACTGRP(*NO)
H BNDDIR('ERRFUNC')
/copy QRPGLESRC,ERRFUNCPR
D Message DS LIKEDS(ErrorMsg)
D success S 3I 0
D failure S 3I 0
D diagAll S 32740A VARYING
D displayText S 52A
D dummyItem DS INZ
D out1 7S 0
D out2 13A
D out3 4A
D out4 7S 2
D out5 5S 4
D SQLDiagData DS INZ
D MessageID 10A
D MessageID1 7A
D MessageID2 7A
D MessageLength 5I 0
D MessageText 32740A VARYING
D otherType 32740A VARYING
D ReturnedSQLCode...
D 5A
D ReturnedSQLState...
D 5A
D RowCount 10I 0
P Main B
DSPLY 'Beginning of test';
TestDummyUDTF();
TestDiagnoticsFromUDTF();
TestDiagnoticsFromBIF();
DSPLY ('Successful tests: ' + %char(success));
DSPLY ('Failed tests: ' + %char(failure));
RETURN;
BEGSR *PSSR;
DUMP;
DSPLY 'Unexpected error while running tests.';
GetErrorMsg(Message);
DSPLY ('Message.MsgId: ' + Message.MsgId);
DSPLY (%subst(Message.MsgText:1:52));
RETURN;
ENDSR;
P E
********************************************************************
P TestDiagnoticsFromBIF...
P B
D text S 10A
DSPLY 'Causing error from SUBSTR to read diagnostics.';
EXEC SQL SELECT SUBSTR('ABC', 1, -1) INTO :text
FROM SYSIBM.SYSDUMMY1;
IF SQLSTATE = '00000';
failure +=1;
DSPLY 'Test failed. Data returned, no error.';
ELSEIF SQLSTATE = '02000';
failure +=1;
DSPLY 'Test failed. No error returned.';
ELSE;
ReadSQLDiagnosticsAll();
IF diagAll <> '';
success += 1;
DisplayTextWindow(diagAll : 'CPF9898':'QCPFMSG');
DSPLY 'GET DIAGNOSTICS ALL succeeded.';
ELSE;
failure +=1;
DSPLY 'GET DIAGNOSTICS ALL failed.';
ENDIF;
ReadSQLDiagnosticsAllCondition();
IF diagAll <> '';
success += 1;
DisplayTextWindow(diagAll : 'CPF9898':'QCPFMSG');
DSPLY 'GET DIAGNOSTICS ALL CONDITION succeeded.';
ELSE;
failure +=1;
DSPLY 'GET DIAGNOSTICS ALL CONDITION failed.';
ENDIF;
ReadSQLDiagnostics();
IF ReturnedSQLCode <> '-138 ' OR
ReturnedSQLState <> '22011';
failure +=1;
DSPLY 'Test failed. Unexpected diagnostic.';
displayText = 'SQLCODE ' + ReturnedSQLCode +
' SQLSTATE ' + ReturnedSQLState;
DSPLY displayText;
ELSE;
displayText = MessageText;
DSPLY displayText;
displayText = 'MESSAGE_LENGTH ' + %char(MessageLength);
DSPLY displayText;
displayText = 'DB2_MESSAGE_ID ' + MessageID +
' DB2_MESSAGE_ID1 ' + MessageID1;
DSPLY displayText;
displayText = 'DB2_MESSAGE_ID2 ' + MessageID2;
DSPLY displayText;
IF MessageText <> '';
success += 1;
DSPLY 'GET DIAGNOSTICS MessageText succeeded.';
ELSE;
failure +=1;
DSPLY 'GET DIAGNOSTICS MessageText failed.';
ENDIF;
ENDIF;
ENDIF;
P E
********************************************************************
P TestDiagnoticsFromUDTF...
P B
D var2 S 7A
D var2_n S 5I 0
var2_n = -1;
DSPLY 'Causing error from DummyUDTF to read diagnostics.';
EXEC SQL DECLARE CUR_DIAG_UDTF CURSOR FOR (
SELECT ITEMS.*
FROM TABLE (QGPL.DummyUDTF(22, :var2 :var2_n, 'ZZ')) AS ITEMS
);
EXEC SQL OPEN CUR_DIAG_UDTF;
DOW 1=1;
EXEC SQL FETCH CUR_DIAG_UDTF INTO :dummyItem;
IF SQLSTATE = '00000';
failure +=1;
DSPLY 'Test failed. Records returned, no error.';
LEAVE;
ELSEIF SQLSTATE = '02000';
failure +=1;
DSPLY 'Test failed. No error returned.';
LEAVE;
ELSE;
ReadSQLDiagnosticsAll();
IF diagAll <> '';
success += 1;
DisplayTextWindow(diagAll : 'CPF9898':'QCPFMSG');
DSPLY 'GET DIAGNOSTICS ALL succeeded.';
ELSE;
failure +=1;
DSPLY 'GET DIAGNOSTICS ALL failed.';
ENDIF;
ReadSQLDiagnosticsAllCondition();
IF diagAll <> '';
success += 1;
DisplayTextWindow(diagAll : 'CPF9898':'QCPFMSG');
DSPLY 'GET DIAGNOSTICS ALL CONDITION succeeded.';
ELSE;
failure +=1;
DSPLY 'GET DIAGNOSTICS ALL CONDITION failed.';
ENDIF;
ReadSQLDiagnosticsUDTF();
IF ReturnedSQLCode <> '-443 ' OR
ReturnedSQLState <> '90002';
failure +=1;
DSPLY 'Test failed. Unexpected diagnostic.';
displayText = 'SQLCODE ' + ReturnedSQLCode +
' SQLSTATE ' + ReturnedSQLState;
DSPLY displayText;
ELSE;
* DSPLY MessageID;
* DSPLY %char(MessageLength);
displayText = MessageText;
DSPLY displayText;
displayText = 'MESSAGE_LENGTH ' + OtherType;
DSPLY displayText;
displayText = 'DB2_MESSAGE_ID ' + MessageID +
' DB2_MESSAGE_ID1 ' + MessageID1;
DSPLY displayText;
IF MessageText <> '';
success += 1;
DSPLY 'GET DIAGNOSTICS MessageText succeeded.';
ELSE;
failure +=1;
DSPLY 'GET DIAGNOSTICS MessageText failed.';
ENDIF;
ENDIF;
LEAVE;
ENDIF;
ENDDO;
EXEC SQL CLOSE CUR_DIAG_UDTF;
P E
********************************************************************
P TestDummyUDTF...
P B
D expected DS DIM(3) LIKEDS(dummyItem)
D index S 3I 0
expected(1).out1 = 22;
expected(1).out2 = 'AAAA';
expected(1).out3 = 'ZZ';
expected(1).out4 = 350.05;
expected(1).out5 = .65;
expected(2).out1 = 219;
expected(2).out2 = 'ABCDEF';
expected(2).out3 = 'ZYXW';
expected(2).out4 = 20.1;
expected(2).out5 = .65;
expected(3).out1 = 438;
expected(3).out2 = 'GHIJKLMNOP';
expected(3).out3 = 'ZYXW';
expected(3).out4 = 2.95;
expected(3).out5 = 0;
DSPLY 'Running test on DummyUDTF, success expected.';
EXEC SQL DECLARE CUR_UDTF CURSOR FOR (
SELECT ITEMS.*
FROM TABLE (QGPL.DummyUDTF(22, 'AAAA', 'ZZ')) AS ITEMS
);
EXEC SQL OPEN CUR_UDTF;
DOW 1=1;
EXEC SQL FETCH CUR_UDTF INTO :dummyItem;
IF SQLSTATE = '00000';
index += 1;
IF out1 <> expected(index).out1 OR
out2 <> expected(index).out2 OR
out3 <> expected(index).out3 OR
out4 <> expected(index).out4 OR
out5 <> expected(index).out5;
failure +=1;
DSPLY 'Test failed. Data mismatch from UDTF.';
ELSE;
success += 1;
DSPLY 'Test succeeded.';
ENDIF;
LEAVE;
ELSEIF SQLSTATE = '02000';
IF index = 0;
failure +=1;
DSPLY 'Test failed. No records returned.';
ENDIF;
LEAVE;
ELSE;
failure +=1;
DSPLY ('Test failed. SQLSTATE ' + SQLSTATE);
// ReadSQLDiagnostics();
LEAVE;
ENDIF;
ENDDO;
EXEC SQL CLOSE CUR_UDTF;
RETURN;
P E
**************************************************************************
P ReadSQLDiagnostics...
P B
D PI
D condition# 5I 0 VALUE OPTIONS(*NOPASS)
IF %parms = 0 OR condition# < 1;
condition# = 1;
ENDIF;
EXEC SQL GET DIAGNOSTICS CONDITION :condition#
:SQLDiagData.ReturnedSQLCode = DB2_RETURNED_SQLCODE,
:SQLDiagData.ReturnedSQLState = RETURNED_SQLSTATE,
:SQLDiagData.MessageText = MESSAGE_TEXT,
:SQLDiagData.MessageLength = MESSAGE_LENGTH,
:SQLDiagData.MessageID = DB2_MESSAGE_ID,
:SQLDiagData.MessageID1 = DB2_MESSAGE_ID1,
:SQLDiagData.MessageID2 = DB2_MESSAGE_ID2;
RETURN;
P E
**************************************************************************
P ReadSQLDiagnosticsUDTF...
P B
D PI
D condition# 5I 0 VALUE OPTIONS(*NOPASS)
IF %parms = 0 OR condition# < 1;
condition# = 1;
ENDIF;
EXEC SQL GET DIAGNOSTICS CONDITION :condition#
:SQLDiagData.ReturnedSQLCode = DB2_RETURNED_SQLCODE,
:SQLDiagData.ReturnedSQLState = RETURNED_SQLSTATE,
:SQLDiagData.MessageText = MESSAGE_TEXT,
:SQLDiagData.otherType = MESSAGE_LENGTH,
:SQLDiagData.MessageID = DB2_MESSAGE_ID,
:SQLDiagData.MessageID1 = DB2_MESSAGE_ID1;
// :SQLDiagData.MessageLength = MESSAGE_LENGTH,
// :SQLDiagData.MessageID = DB2_MESSAGE_ID;
RETURN;
P E
************************4*************************************************
P ReadSQLDiagnosticsAll...
P B
EXEC SQL GET DIAGNOSTICS :diagAll = ALL;
RETURN;
P E
**************************************************************************
P ReadSQLDiagnosticsAllCondition...
P B
D PI
D condition# 5I 0 VALUE OPTIONS(*NOPASS)
IF %parms = 0 OR condition# < 1;
condition# = 1;
ENDIF;
EXEC SQL GET DIAGNOSTICS :diagAll = ALL CONDITION;
RETURN;
P E
P DisplayTextWindow...
P B Export
D PI
D Text 8192 Const Varying
D MessageId 7 Const
D MessageFile 21 Const
D ErrCode DS
D BytesIn 10I 0 INZ(0)
D BytesOut 10I 0 INZ(0)
DQUILNGTX PR EXTPGM('QUILNGTX')
D MsgText 8192 CONST
D MsgLength 10I 0 CONST
D MessageId 7 CONST
D MessageFile 21 CONST
D dsErrCode LIKE(ErrCode)
QUILNGTX(Text : %Len(Text) :
MessageId : MessageFile : ErrCode);
P E
Upvotes: 2
Views: 1721
Answers (1)
Charles
Reputation: 23793
Yeah, it looks like it could be a bug...or given the PTF you linked to...it could be a limitation of GET DIAGNOSTICS when working with external UDTFs.
I see the same thing on 7.2.
What PTF level's are you at? If not current, IBM will ask you to get current.
You can report the issue here: https://www-946.ibm.com/support/servicerequest/Home.action
IBM will likely ask for code to reproduce. You'll probably want to cut down your example to the bare minimum to reproduce. Actually, it's s good idea to do so beofre posting to stackoverflow the next time.
As an example, here's all that's needed when calling the UDTF to see the problem:
**free
ctl-opt actgrp(*NEW) main(mainline) cvtopt(*NOVARCHAR) debug(*yes);
dcl-pr mainline extpgm('TESTRPGLE');
end-pr;
dcl-proc mainline;
dcl-s diagAll varchar(32740);
dcl-ds dummyItem qualified;
out1 zoned(7);
out2 varchar(13);
out3 varchar(4);
out4 zoned(7:2);
out5 zoned(5:4);
end-ds;
dcl-s nullArr int(5) dim(5);
exec sql
declare C1 cursor for (
SELECT ITEMS.*
FROM TABLE (DummyUDTF(22, cast(null as varchar(7)), 'ZZ')) AS ITEMS
);
exec sql
open C1;
//I expected SQLSTATE to be 90002 here, but it's not
if sqlstate <> *ZEROS;
exec sql
get diagnostics :diagAll = ALL;
dump;
return;
endif;
exec sql
fetch C1 into :dummyItem :nullArr;
//here's where the SQLSTATE of 90002 is returned.
if sqlstate <> *ZEROS;
exec sql
get diagnostics :diagAll = ALL;
dump;
return;
endif;
return;
end-proc;
side note: start using fully free format ;)
Upvotes: 3
Related Questions
- DB2 SQL How to get the last executed SQL-Statement with GET DIAGNOSTICS?
- Output for DSPPGMREF is incorrect for some SQL objects
- DB2 AS/400 Querying with Go
- DB2 SQL - Issue with SQLCODE
- Debugging DB2 Session
- logic behind SQL code not clear
- SQL -302 on OPEN cursor in SQLRPGLE
- Query/400 Unmatched scenario
- Initialize / activate SQL prior to GET DIAGNOSTICS
- Issue maybe IBM I DB2 Query SQL