Hi All,

I have one interesting requirement. There are two unique indexes defined on a table. My program deals with inserts and deletes. This program is a kind of delta process to keep target tables in sync with Source tables.

Basically if insert fails, this program deltes the old record and insert it again. (To keep the updates on source table in target table.).. offcourse..this is just a logic. and there is some other stuff too. If key K1 is duplicated, delete the old record and insert the new one. If key k2 is duplicated, then abend the job. So when i get -803 i need to know which Key K1 or K2 caused the negative error code. Hope this is clear

My question is.

If i get -803 while inserting, I need to find out which key resulted in duplicate insert. As i mentioned there are two unique keys.

I have two solutions at this point of time.

1. If we use DSNTIAR to get the diagnostic information it would give the index name, based upon this index name we can figure out which key caused the duplicate. However i dont want to use this method since i have to invoke this error routine whenever i get -803.

2. When i get -803, put a select statement with where predicate for key K2. if it exists abend the job or else continue with the usual process.

Most of the times, My program deals with 80% of the updates. So i dont want to go by the above two methods as it is going to be additional overhead.

Is there any way i can find out the column no or column name directly with out doing the above said process.

Note: The above two methods are just upto my knowledge. If you guys have any additional logic that would be really great for me.

Please correct me if am wrong.

Thanks,
Vinay

When you call DSNTIAR you are passing the SQLCA (SQL Communication Area), which should contain the information you need.

If you are using COBOL, you should have the following in your program:
INCLUDE SQLCA

When you execute an SQL statement, all return code information and tokens are placed into the SQLCA.

See Appendix C. SQLCA and SQLDA of the SQL Reference Manual for a description of the SQLCA fields. I believe that the index name will be somewhere in the SQLERRMC field. If you need the actual column name(s), then you could use the index name and look up the columns in SYSIBM.SYSKEYS.

Thank you Marcus.

I use PL1 program and am passing SQLCA to DSNTIAR. Once i get the index name i can directly decide which key caused this. Why to query SYSIBM.SYSKEYS ..? if i get the index name as I2 then from the DDL it is understood that it is for K2. I mean i can hard code it like

IF I_NAME = 'I2' THEN CALL PLIRETC(16) --> (say I2 is defined for K2)
ELSE CALL CONTINUE_PROCESS. (or anything based upon the logic)

Is there any other options where i can get the column name or column no or something like that which can give me the key value causing duplicate..?

I understand that you may not need the index column names, and the index name is enough. In that case, you do not need to access SYSIBM.SYSKEYS.

Just to make sure you understand what I said previously, you do not need to call DSNTIAR because the index name is located in SQLCA, probably in SQLERRMC field. It may not be the only value in SQLERRMC if there are multiple tokens.

Yeah. I got your point. I have to search for INDEX name in the SQLERRMC field. Thank you Marcus. It would be great help for me if you can help me with this link. Thanks in advance.

www.dbforums.com/showthread.php?t=1614441