DSXchange

Hi all,

I am having a problem with DB2 API stage. I searched the forum could not find solution for it. Here is the job.

Seq file --> Copy Stage --> DB2 API (update)

Job is reading from a flat file and updating the records in DB2 table. Here DB2 errors are getting captured as warnings instead of fatal errors. So the job never abends even if there are errors while updating the table. Recently there was a scenario where the target table was locked by some process and the datastage job was trying to update the table at the same time. It got SQL error message but it logged as warning message and finally job completed fine.

Here is the error message:

DB2_Prtn_Phy_Cntrl,0: Warning: BCEJB580P.DB2_Prtn_Phy_Cntrl: [IBM][CLI Driver][DB2] SQL0904N Unsuccessful execution caused by an unavailable resource. Reason code: "00C90097", type of resource: "00000200", and resource name: "DPMGSQ .SPMGSQ2". SQLSTATE=57011

Work around could be to use dsjob -logsum command and scan through the log messages for SQL error codes and force the job to abort,

/apps/Ascential/DataStage/DSEngine/bin/dsjob -logsum -type WARNING $Project $JobName | grep "SQL[0-9][0-9][0-9][0-9]N"

There are some problems with this workaround as well, we need to clear the joblogs for every run.

Is there any environment variable or any setting that I can do in DB2 API stage to log these warnings as errors?

Thanks in advance,
Surya

I doubt there is such an option. But you need to re-think your approach. Don't let warnings occur in the first place. Treat all warnings as reg signals. If warnings are more like informational messages then demote them as one, using the message handler. But with warnings such as resource constraints, the job should abend.

Welcome aboard.

Do you mean something like "treat warnings as fatal"?

I don't think this exists in the DB2 API stage. Is there some reason you're not using the DB2 Enterprise stage?

ray.wurlod wrote:Welcome aboard.

Do you mean something like "treat warnings as fatal"?

I don't think this exists in the DB2 API stage. Is there some reason you're not using the DB2 Enterprise stage? ...

Hi Ray,

Job has to update table that resides on mainframe host, so we cannot use enterprise stage to connect to that database.

DSguru,
"Treat all warnings as reg signals" can you tell me how do we do this? Job is abending if there are any issues connecting to the database but it is not failing if the table is locked by any process.

Thanks,
Surya

Surya wrote:Hi all,

I am having a problem with DB2 API stage. I searched the forum could not find solution for it. Here is the job.

Seq file --> Copy Stage --> DB2 API (update)

Job is reading from a flat file and updating the records in DB2 table. Here DB2 errors are getting captured as warnings instead of fatal errors. So the job never abends even if there are errors while updating the table. Recently there was a scenario where the target table was locked by some process and the datastage job was trying to update the table at the same time. It got SQL error message but it logged as warning message and finally job completed fine.

Here is the error message:

DB2_Prtn_Phy_Cntrl,0: Warning: BCEJB580P.DB2_Prtn_Phy_Cntrl: [IBM][CLI Driver][DB2] SQL0904N Unsuccessful execution caused by an unavailable resource. Reason code: "00C90097", type of resource: "00000200", and resource name: "DPMGSQ .SPMGSQ2". SQLSTATE=57011

Work around could be to use dsjob -logsum command and scan through the log messages for SQL error codes and force the job to abort,

/apps/Ascential/DataStage/DSEngine/bin/dsjob -logsum -type WARNING $Project $JobName | grep "SQL[0-9][0-9][0-9][0-9]N"

There are some problems with this workaround as well, we need to clear the joblogs for every run.

Is there any environment variable or any setting that I can do in DB2 API stage to log these warnings as errors?

Thanks in advance,
Surya

In the above error msg you got is -904. So if you include -904 as fatal error in the stage, whenever it encounters a -904 from DB2, this job is going to fail. You can include all possible DB2 -SQL codes as either warnings or fatal errors. If included as warning, job contines fine, else it fails. Hope this helps.

Hi,

Finally after searching a lot in Basic manual I was able to find workaround for this problem. It can be done with only one after job routine. After job routine will capture the current run log messages and abort the job if it finds SQL code in it. Matchfield and Match are the functions which will do pattern matching.

Here is sample code:
=============

DIJobHandle = DSJ.ME

StartTimestamp = DSGetJobInfo(DIJobHandle, DSJ.JOBSTARTTIMESTAMP)
EndTimestamp = DSGetJobInfo(DIJobHandle, DSJ.JOBLASTTIMESTAMP)

Call DSLogInfo("Job Start timestamp :":StartTimestamp, DSJ.ME)

Call DSLogInfo("Job End timestamp :":EndTimestamp, DSJ.ME)

SummaryArray = DSGetLogSummary (DIJobHandle, DSJ.LOGANY,StartTimestamp , EndTimestamp , 50)

ErrorCode=MATCHFIELD(SummaryArray,"0X'SQL'4N'N'0X",2):MATCHFIELD(SummaryArray,"0X'SQL'4N'N'0X",3):MATCHFIELD(SummaryArray,"0X'SQL'4N'N'0X",4)

if ErrorCode MATCH "'SQL'4N'N'" then
Call DSLogFatal("SQL error message trapped:":ErrorCode:"Please check previous messages for error details",DSJ.ME)
end
Call DSLogInfo("Log Summary:":SummaryArray, DSJ.ME).

-Surya

vijayrc wrote:

Surya wrote:Hi all,

I am having a problem with DB2 API stage. I searched the forum could not find solution for it. Here is the job.

Seq file --> Copy Stage --> DB2 API (update)

Job is reading from a flat file and updating the records in DB2 table. Here DB2 errors are getting captured as warnings instead of fatal errors. So the job never abends even if there are errors while updating the table. Recently there was a scenario where the target table was locked by some process and the datastage job was trying to update the table at the same time. It got SQL error message but it logged as warning message and finally job completed fine.

Here is the error message:

DB2_Prtn_Phy_Cntrl,0: Warning: BCEJB580P.DB2_Prtn_Phy_Cntrl: [IBM][CLI Driver][DB2] SQL0904N Unsuccessful execution caused by an unavailable resource. Reason code: "00C90097", type of resource: "00000200", and resource name: "DPMGSQ .SPMGSQ2". SQLSTATE=57011

Work around could be to use dsjob -logsum command and scan through the log messages for SQL error codes and force the job to abort,

/apps/Ascential/DataStage/DSEngine/bin/dsjob -logsum -type WARNING $Project $JobName | grep "SQL[0-9][0-9][0-9][0-9]N"

There are some problems with this workaround as well, we need to clear the joblogs for every run.

Is there any environment variable or any setting that I can do in DB2 API stage to log these warnings as errors?

Thanks in advance,
Surya

In the above error msg you got is -904. So if you include -904 as fatal error in the stage, whenever it encounters a -904 from DB2, this job is going to fail. You can include all possible DB2 -SQL codes as either warnings or fatal errors. If included as warning, job contines fine, else it fails. Hope this helps.

vijayrc wrote:.....if you include -904 as fatal error in the stage, whenever it encounters a -904 from DB2, this job is going to fail. You can include all possible DB2 -SQL codes as either warnings or fatal errors. If included as warning, job contines fine, else it fails. Hope this helps.

Hi Vijay,

I am stuck in a similar problem..
Can u please guide me How to include a-904 as fatal error in the stage ?
I want to know , that how do we add some SQL codes in the stage ??

I am using DataStage Server Edition on Windows, for DB2 on Mainframes z/OS...

Thanks

What part of Surya's code is unclear? It looks like quite an elegant solution.