Trapping the SQLCODE in COBOL generated by MVS

yksjosh · Post by **yksjosh** » Mon Sep 25, 2006 6:05 am

Hello Everyone,

I have a developed a job in DS MVS, in which i am reading records from an input file and inserting them into a Table.

But my code stops working if the database (DB2 on mainframe) rejects the row due to Duplicate constraint.

I dont want to do a look up (on the value which i am inserting for its existance in the table), before inserting the record into the table.
Infact I want to take decision depending on the SQLCODE.

The problem is when you generate the code the insert & SQLCODE part looks like this

EXEC SQL
INSERT INTO CLIENT(
CLIENT_NBR,
CHANGED_IND,
CONVERTED_DT,
ADD_IND
) VALUES (
:HOSTVARS1.HV-CLIENT-NBR-DS162
:HOSTVARS1.HV-CLIENT-NBR-DS162-I,
:HOSTVARS1.HV-CHANGED-IND-DS166
:HOSTVARS1.HV-CHANGED-IND-DS166-I,
:HOSTVARS1.HV-CONVERTED-DT-DS169
:HOSTVARS1.HV-CONVERTED-DT-DS169-I,
:HOSTVARS1.HV-ADD-IND-DS171
:HOSTVARS1.HV-ADD-IND-DS171-I
)
END-EXEC

MOVE SQLCODE TO LOCALE-SQLCODE IN INS1
IF SQLCODE NOT = 0 THEN
PERFORM DB-ERROR
THRU DB-ERROR-EXIT
MOVE 4 TO EXIT-STATUS
ELSE
ADD 1 TO RECORD-COUNT IN INS1
END-IF

The problem is if the SQLCODE is -803 if the insert fails due to duplicate, but Datastage is not giving me the flexibility to trap the same. Instead it automatically calls the DB-ERROR module and the code exits the execution.

If anyone has any idea how to do this or is some body is sure that its the limitation of the DS MVS please let me know.

Many thanks in advance.

Yogesh

Mike · Post by **Mike** » Mon Sep 25, 2006 6:53 am

It's not possible in a mainframe job. A -803 SQLCODE will ABEND the job. You need to avoid the duplicate insert (lookup or join to detect existence). If more robust functionality is needed, then you will need to use a server or parallel job.

Mike

yksjosh · Post by **yksjosh** » Mon Sep 25, 2006 7:12 am

Mike,

My code is not getting ABEND, the return code of the program execution is 4

There are 12 records in my input file

Report file reads as follows

Records read from DDNAME DD00002 001
000 records have been inserted into table CLIENT_REF
Records written to DDNAME DD00003 001
Records written to DDNAME DD00004 001
PROCESS COMPLETED WITH RC= 4
DD00002 is the input file
DD00003 is the file in which i write the records which were rejected from the table (link constraint is SQLCA.SQLCODE <> 0)
DD00004 is the file in which I am just writing the data

the link execution order is
1.DB Table
2.DD00003
3.DD00004

Mike · Post by **Mike** » Mon Sep 25, 2006 7:41 am

Maybe not a hard ABEND, but the job stopped processing after the first database error (I haven't touched a mainframe in a couple of years now, so my memory of details is a bit fuzzy

). You should design a mainframe job so that it always ends with RC=0 (All warnings should be handled by your job design). You will need to implement some kind of existence check. The best method will depend on your data volumes.

Mike

yksjosh · Post by **yksjosh** » Mon Sep 25, 2006 7:52 am

Mike,

After lots of trying the work around on which we have come is like this

We are developing an external routine which excepts these four input fields that needs to be inserted into the table.
and will return a flag depending upon the SQLCODE whether the insert was successful or not.
We can than do our later processing depending on this flag.
(I am not sure if the insert fails inside the external routine, does the mainframe job still stops executing . I think that will be clear after we had developed the same

)

Let me know if you can think of any possible condition in which this work around can fail.

Thanks for all the inputs.

Cheers
Yogesh

Mike · Post by **Mike** » Mon Sep 25, 2006 8:00 am

An external routine is definitely NOT recommended for this situation. If you're going to write code, why did you buy the ETL tool? Why not just code the whole program by hand?

Mike

yksjosh · Post by **yksjosh** » Mon Sep 25, 2006 8:17 am

Mike I agree,

I had posted regarding this doubt before also.
here is some text from same.

According to Ray Sir, the solution is working fine of checking if the key is already existing in the table but i am just trying to figure out the scenerio where an insert by other process into the same table might be possible during the time between the look up(to find if the key already exists) and Insert (if key doesn't exist).
Because we might be running more than one instances of this program on similar input files.And all these process try to access this common client table.

I implimented the code using look up (before inserting) thinking that we wont be running more than one instance of the program. But now we are running more than one processes so i tried a lot finding if i can trap the SQLCODE.
Nothing else seems to help other than external routine at the moment

.

But somehow I am very much surprised and not much convinced, that such a big tool has not given the flexibility to play with the SQLCODE which according to mainframe and cobol guys here, is a very basic thing in cobol programing which consists of interaction with database

.

Regards
Yogesh