GET DIAGNOSTICS message text

[tdavis]

I am dynamically creating an SQL statement that will contain info from user input. The user enters the file name and library name, and optionally a filtering clause such as TRANDATE > 2022-12-31. My program constructs the SQL statement and executes it. I am actually creating a view. All of this works fine. My problem is error capturing. I am using GET DIAGNOSTICS to get information back about the success or failure of the statement. That too works fine, but the MESSAGE_TEXT returned, in this example, is "Prepared statement S1 not found.". I am forcing this error for testing purposes by entering a field name that does not exist, such as XXXXXX. If I look in the joblog I see a diagnostic message that says "Column or global variable XXXXXX not found.". That is what I want to return to the user, but I can't find any way to get that using any of the condition 1 variables.

Is it even possible?

command = 'Select * from ' +
%trim(parm_liblname) + '/' +
%trim(parm_filename) + ' Where ' +
%trim(parm_filters);

exec SQL Prepare S1 From : command;
exec SQL Declare FCursor INSENSITIVE Cursor for S1;
exec SQL Open FCursor;
exec SQL Get Diagnostics :sqlrecordcnt = DB2_NUMBER_ROWS;

exec SQL Get Diagnostics Condition 1
:sql_code = DB2_RETURNED_SQLCODE,
:sql_state = RETURNED_SQLSTATE,
:sql_msglen = MESSAGE_LENGTH,
:sql_msgtext = MESSAGE_TEXT,
:sql_msgid1 = DB2_MESSAGE_ID,
:sql_msgid2 = DB2_MESSAGE_ID1,
:sql_msgid3 = DB2_MESSAGE_ID2;

[B.Hauser]

If you are executing the GET DIAGNOSTIC Statement after the OPEN you'll get the Error Message of the OPEN.
The error you want to get however is caused by the PREPARE Statement.

BTW I'm just curisous how you want to handle the dynamic field selection in RPG ... I haven't seen a SQLDA (Descriptor Area) yet in your examples.
Or are the files always return the same columns in the same sequence?

[tdavis]

Hi Brigitta,

Thank you so much for your response. I have added GET DIAGNOSTICS after the PREPARE statement and the
result is as you say. The message returned is exactly what is needed.

To answer your question, I am selecting all fields in the file using an asterisk after the SELECT.
Our purpose here is to create mulitple views over a physical file that has a lot of data, for example
100 million records, so that they can then be copied in chunks concurrently. This is apparently faster
per the requestors of this utility that I am writing.

// Assemble CREATE VIEW SQL statement
command = 'Create VIEW ' +
%trim(libraryname) + '/' + %trim(viewfile) + ' ' +
'for system name ' + %trim(sysfilename) + ' ' +
'as Select * From ' + %trim(liblname) + '/' + %trim(filename) + ' ' +
'Where ' +
'RRN(' + %trim(filename) + ') >= ' + %char(start) + ' and ' +
'RRN(' + %trim(filename) + ') <= ' + %char(end) ;

Thanks again, Tony.

[B.Hauser]

You are creating hundreds of views (dynamically?) for selecting a specific range of rows based on hardcoded relative record no?
Have you already thought about having only a single view and adding the where conditions at runtime?
... or if you want to keep the where conditions have you thought about using SQL Global Variables? Then you can have a single view, and set the global variables just before calling it

Something link this:

CREATE OR REPLACE VARIABLE YourSchema.GblVarFromRRN  BigInt Default 0;
CREATE OR REPLACE VARIABLE YourSchema.GblVarToRRN  BigInt Default 0;

CREATE VIEW ...
   SELECT * FROM FILE
   WHERE RRN(File) >= GblVarFromRRN and GblVarToRRN;

Runtime:
Set GblVarFromRRN = 1000;
Set GblVarToRRN  = 10000;
Select * from YourView;

... on the other side if you only want to copy the "content" of the view, there is no need to create and read a cursor.

I do not know your application, but why do you want to copy millions of records?

[tdavis]

Our database team uses Data Mirror to mirror DB2 to the data warehouse, which is an Oracle platform. They want to discontinue using Data Mirror because they also have Golden Gate. They say that Golden Gate is more efficient and cheaper, but has one flaw. When doing an initial load of a large file it does not perform well. Their first request was to have me copy these larger files to the IFS in said chunks via views. Copying the entire file versus copying from views took the exact same time, plus they then have to copy anyway from the IFS. So they then figured out how to copy directly from DB2 to Oracle, but want to run concurrent copies in 20 million record views (for example over a 100 million record file). Creating that many views is fast and they will have to delete them immediately after the copies complete.

[Vectorspace]

Copying the entire file, or creating multiple views to specify specific rrn ranges and using those to parallelise the copy. Are those really the only two options?

Is that because Golden Gate can only do a "select * from myTable;" with no where clause, so can only copy whole files/views?

Can Golden Gate not do "select * from myTable f where rrn(f) >= 1001 rrn(f) <= 2000"