QSH Error Trapping

[gonen]

Re: QSH Error Trapping

I am running code in version 6 and 7.

The code is actually invoked by Java, which runs SQL ALTER statement.

The issue was raised - when their job was cancelled when they tried to run ALTER command which resulted an inquiry message of "....might cause data loss" . (THIS IS A ONLY GUESS)

What happened is that their job was cancelled since there was no "Reply List Entry" that will override the "cancel" with "ignore"



They tried hard to provide the CPxxxxxx error code (so I will add it to the "Reply List Entry" - but could not find a way to get it.

The only information they could get was "error code 10".



So - I simulated their ALTER command in CL running QSH, with the above code, and got the same "error code 10" with a little more information of

**** CLI ERROR *****
SQLSTATE: 57014
NATIVE ERROR CODE: -952
Processing of the SQL statement ended. Reason code 10.

in the log file.

Still - there was no sign for the CPxxxxx message - anywhere....


Thank you very much for your help

[Scott Klement]

Re: QSH Error Trapping

By V5R3, it's possible to do an ALTER TABLE without leaving CL by calling SQL CLI APIs. It might be possible even before V5R3, but I can't recall trying with CL back then.

V5R3 is the first release where you can call SQL CLI directly from CL (you need the *BYVAL keyword on CALLPRC, which was added in V5R3). Not too many people used this functionality (though, I thought it was pretty neat, myself.)

But many people had worked around this by writing simple 'RUN SQL' commands in RPG that you could pass an SQL statement to, and it'd run them... so you'd just call those commands from your CLs. Other people would use RUNSQLSTM. Some found calling the SQL statement via QShell to be the easiest option, since it didn't require a special RPG program or RUNSQLSTM (which is awful for error handling, et al, and requires a separate source member.)

Nowadays, of course, you'd use the IBM-supplied RUNSQL command.

[Scott Klement]

Re: QSH Error Trapping

The code is actually invoked by Java, which runs SQL ALTER statement.

But... Java typically uses the JDBC library to run SQL statements. That's a completely different animal than QShell. No matter what we teach you about QShell error handling, it's not going to help you in a Java program!!!

What happened is that their job was cancelled since there was no "Reply List Entry" that will override the "cancel" with "ignore"

That's not how it works! You can trap any error in Java using the try/catch construct. It wouldn't "end the job" unless you told your Java methods to propagate the error all the way back to the caller.

They tried hard to provide the CPxxxxxx error code (so I will add it to the "Reply List Entry" - but could not find a way to get it.

You don't want/need a reply list entry to trap an error in Java or QShell. This doesn't make any sense. The msgid of the error message would be SQL0952, it would not start with a "CP", you already know this, you told us in the very first message of this thread. But that would NOT be sent as an error you can trap or set a reply-list entry for, because it's never sent to the Java program (or QShell for that matter) as an exception message. In Java it's sent as a normal Java error that you trap with try/catch, like any other error. In QShell, it's printed on the screen, and the exit status is set to reflect that an error occurred.

[tomliotta]

Re: QSH Error Trapping

(you need the *BYVAL keyword on CALLPRC, which was added in V5R3).

Ah, yes. I thought there might be a reason I didn't do it. It was long enough ago that I couldn't remember *BYVAL and V5R3.

But many people had worked around this by writing simple 'RUN SQL' commands in RPG...

I made one many years ago in REXX just to see how it could be done. I saw someone do it in RPG and wondered how much could be done without affording compilers (which I personally couldn't).

As soon as I saw it was possible with the SQL CLI APIs (apparently at the start of V5R3), I started to switch. I never understood how ODBC and JDBC were used so often; but essentially the same APIs in SQL CLI were mostly ignored, even in sites without the SQL Dev Kit.

The code is actually invoked by Java, which runs SQL ALTER statement.

So - I simulated their ALTER command in CL running QSH, with the above code, and got the same "error code 10" with a little more information of in the log file.

Ah! That makes a more sense.

In that case, never mind my previous comments. Purely out of confusion about what was needed.

Tom

[gonen]

Re: QSH Error Trapping

I learned a lot now.


Since I have no direct contact with the Java people, I tried to simulate their case.

After long investigation I can out with this:

Honestly -Java is not my area - but I managed to solve the problem this way:

1 - ADDENVVAR ENVVAR(QIBM_CHILD_JOB_SNDINQMSG) VALUE(1)
2 - I ran their script

in other session I saw their JOB as - QZSHSH waiting on MSGW
I gave it a Go

another job started - QP0ZSPWP waiting on MSGW
I gave it a Go

In the JOBLOG of the QP0ZSPWP job I found this:

Display Spooled File
File . . . . . : QPJOBLOG Page/Line 1/1
Control . . . . . Columns 1 - 78
Find . . . . . .
*...+....1....+....2....+....3....+....4....+....5 ....+....6....+....7....+...
5770SS1 V7R1M0 100423 Job Log
Job name . . . . . . . . . . : QP0ZSPWP User . . . . . . : QUSER
Job description . . . . . . : AVNERJOBD Library . . . . . : QGPL
MSGID TYPE SEV DATE TIME FROM PGM
CPF1124 Information 00 23/05/13 17:27:58.507065 QWTPIIPP
Message . . . . : Job 353220/QUSER/QP0Z
in subsystem QINTER in QSYS. Job entere
CPD32CC Diagnostic 20 23/05/13 17:31:47.004321 QDBCHGFI
From user . . . . . . . . . : RIGONEN1
From module . . . . . . . . : QDBCHGFI
From procedure . . . . . . : SEND_DIAG
Statement . . . . . . . . . : 36977
To module . . . . . . . . . : QSQALTER
To procedure . . . . . . . : QSQALTER
Statement . . . . . . . . . : 10039
Message . . . . : Change to field ACTIV......

I added an entry in the Reply List for CPD32CC with "I" (for Ignore)

I Deleted the QIBM_CHILD_JOB_SNDINQMSG variable




That solved the problem.



I hope it might help someone.


Thanks for a great replies.

[DeadManWalks]

Re: QSH Error Trapping

Ya but does it work. And by work I don't mean handling the error message but does the alter table still happen?

[gonen]

Re: QSH Error Trapping

yep. It did ....