Generic DB2 SQLCODE handle for cobol



IBM's flagship relational database management system

Generic DB2 SQLCODE handle for cobol

Postby mandor » Thu Apr 05, 2012 4:57 pm

Hello!

Me and my colleague are wondering if it is at all possible to generically handle sqlcode.
What we want in fact is to call a routine that will get the parameters of where clause of an unsuccessful SQL.
I.E.:

EXEC SQL
   SELECT 1 FROM SOME_TABLE
   WHERE CENTER = :FIRM
      AND UNIT = :ORG-UNIT
      AND EMPLOYEE STATUS = :STATUS
END-EXEC

IF SQLCODE NOT = 0 THEN
   PERFORM SQL-HANDLE
   MOVE SQL-HANDLE-OUT TO MESSAGE
END-IF


So that MESSAGE contains something like 'PAR1: 7; PAR2: 16; PAR3 = 99;'

So, F1, guys! :-D

Anything is helpful!
Thanks in advance!

Kind regards,
Mario.
mandor
 
Posts: 28
Joined: Wed Feb 23, 2011 4:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Generic DB2 SQLCODE handle for cobol

Postby mandor » Fri Apr 13, 2012 12:46 pm

Bumping the thread.

Please guys, realy ANY idea is most helpful!
Any reading material or the simplest of examples of anything remotely automatic and/or generic.
mandor
 
Posts: 28
Joined: Wed Feb 23, 2011 4:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Generic DB2 SQLCODE handle for cobol

Postby enrico-sorichetti » Fri Apr 13, 2012 1:04 pm

Bumping the thread.
Please guys, realy ANY idea is most helpful!


remember that... replying is
on voluntary base
on our own time and equipment
-- from a legal point of view <generally>
-- using the employer time and equipment for tasks not related to the job
-- is cause for lawful termination
-- replying to forum questions is NOT job related <generally>

based on experience ==> no reason to research from scratch something just for Your needs
interest of the topic
clarity of the requirement

there is no commitment for a reply,
so if You do not get any You will have to live without .

and usually soliciting/pestering for a reply will just piss off people
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Generic DB2 SQLCODE handle for cobol

Postby mandor » Fri Apr 13, 2012 6:20 pm

Sorry to see you pissed off.
I just wanted people to know that anything would help. I wasnt planning on bumping every day.
I just bumped once after a week. A "no way" answer would be just as good and helpful.
mandor
 
Posts: 28
Joined: Wed Feb 23, 2011 4:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Generic DB2 SQLCODE handle for cobol

Postby dick scherrer » Sat Apr 14, 2012 12:07 am

Hello,

I know of no method of capturing the predicates of a query after it has executed (successfully or not).

What many of my clients have done (with whatever database system is used) is to have a callable "db error module" that is invoked whenever a database failure has been detected. This requires that any program that is to use the common module must be modified to CALL the module and present all of the information the called module requires.

It is best done when the system is being developed. It can be implemented over time and does not require a global change. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Generic DB2 SQLCODE handle for cobol

Postby NicC » Sat Apr 14, 2012 1:37 pm

Have you tried looking in the SQL manuals - there is something called 'whenever' and, possibly - cannot remember, another statement that sits alongside
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Generic DB2 SQLCODE handle for cobol

Postby mandor » Sat Apr 14, 2012 2:30 pm

Thanks guys!

Now, by combining these I can set WHENEVER SQLERROR GOTO DB2-ERROR-HANDLE.
This doesn't differ significantly from manualy handling sql errors as there is still a need for handling +100, -803 or -811.
Mr Dick Scherrer, how would one pass the predicates to the db error module? I cannot pass the whole dclgen structure(s) involved with the query as I don't have a way of parsing them genericaly or to unpack the numeric fields.

My application is reasonably flexible and relatively easy to change. And it is a CICS - COBOL - DB2 app.
mandor
 
Posts: 28
Joined: Wed Feb 23, 2011 4:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Generic DB2 SQLCODE handle for cobol

Postby dick scherrer » Sun Apr 15, 2012 5:08 am

Hello,

To get "things" to the called error handling module, each program that is to use it would have to be changed to create working storage fields that contain all of th einto you want to be handled by the common error module.

Dependng on how much you want to present, the amount of data to be formatted and given to the called module could be quite small or very large.

I'm not sure how this would be used. Keep in mind that many selects run into many "lines of code" . . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Generic DB2 SQLCODE handle for cobol

Postby mandor » Mon Apr 16, 2012 6:11 pm

I'm not sure if you understand me, Mr Scherrer.

The simple question is: Is there any way to capture the values of the host variables of the LAST SQL query so they can be written to some log?
mandor
 
Posts: 28
Joined: Wed Feb 23, 2011 4:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Generic DB2 SQLCODE handle for cobol

Postby dick scherrer » Mon Apr 16, 2012 9:48 pm

Hello,

I'm not sure if you understand me, Mr Scherrer.
I believe i do understand you :)

As i posted previously:
I know of no method of capturing the predicates of a query after it has executed (successfully or not).


Not knowing how to get what you want, i mentioned "another" way to get something similar that has been used many, many places with quite a few different database management systems.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post