JDBC Connector Calling Stored Procedure

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
ShaneMuir
Premium Member
Premium Member
Posts: 508
Joined: Tue Jun 15, 2004 5:00 am
Location: London

JDBC Connector Calling Stored Procedure

Post by ShaneMuir »

Hi All

I am having a strange issue when using the JDBC Connector to call a Stored Procedure.

The job design itself is relatively simple:

Code: Select all

 JDBC ---->  Tfm  ----->  Unstructured (Excel)
The only "tricky" part is that it is fully RCP and parameterised.

To execute the process I pass one of about 30 different sql statements in the form of a exec stored procedure with parameter values as required by that stored procedure.

The good news is - this process works. The stored procedure executes and the excel file is populated with the correct datatypes. So far so good.

The ISSUE is - for some reason the stored procedure is getting called multiple times before outputting the data. We know this as the stored procedure has a log which is called once the procedure has finished executing.

From what we can see in this log, it ties up with message notification timings in the Datastage Log. Below is an extract of the DS log messages:
Timestamp Message
25/07/2017 15:28:41 jdbc_RunSQL_read: The transaction isolation level is set to: TRANSACTION_READ_COMMITTED
25/07/2017 15:55:33 jdbc_RunSQL_read: The character set encoding for the non-Unicode character values on the link is windows-1252. The maximum number of bytes per character in this encoding is 1.
25/07/2017 16:05:56 odbc_RunSQL_read,0: The connector established connection to the data source.
For each timestamp there is a log record in our BI reporting log that the stored procedure has executed.

If I execute the same same SQL string locally in SQL it only produces the one BI log message, so it is definitely the JDBC stage which is causing it to execute 3 times.

It wouldn't be so bad, as the jobs do execute, but some of the stored procedures can take about 40 mins to run normally, and there are approx 30 different reports to be run. We can't really afford to have the reports taking 3 times as long as they should.

I have tried pretty much every combination of settings on the JDBC stage to avoid the extra calls, but it just does not seem to work.

Does anybody know why it might execute the stored procedure multiple times? And if there is any setting that might stop it from happening.

In the meantime I am trying to work around by directing the stored procedure to a tmp table in SQL and read from that. But I would prefer as DS centric solution if possible.

Shane.
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

Does your default apt file have 3 nodes? The JDBC Connector does run in parallel and it sounds like it is being triggered three times.

Try right-clicking on the JDBC connector, going to the Stage tab, the Advanced sub-tab and setting Execution mode to Sequential.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
ShaneMuir
Premium Member
Premium Member
Posts: 508
Joined: Tue Jun 15, 2004 5:00 am
Location: London

Post by ShaneMuir »

The multinode/parallel settings were the first thing I checked. Its definitely executing sequentially.

More so it executes the multiple calls sequentially also, 3 times, one after the other.

I have a theory that its going something to do with RCP and the connector calling the DB to get the metadata. But I am not sure how to prove it.
Post Reply