Datastage - iSeries problem

[Huddy]

This is a long shot but hoping someone can help. Not sure if this is the right place.

We are in the process of upgrading DataStage (was Mercator) to version 8.4.0.1. but have a strange problem.

We use datastage to transmit and receive data between the iSeries and Sybase. Nearly all files are sent to an off-line library during overnight batch processing where the data is picked up later by our iSeries application batch process. This works seamlessly.

The exception is we have one real time file that sits in the main database library which is updated during the day. This file receives live transaction data into our application database. It's this process we are having problems with.

During testing, everything worked as expected and there was no reason to believe there would be any problems going live. The project was subsequently signed off and we upgraded a few weeks ago. However, that evening, we couldn't get any data to the online file without an error occurring in DS. As a result the implementation was aborted. We have since tried to find the cause of the error:

SQLError: SQLPrepare: No extended error information found
<4472-1176-07/26/13 15:18:08>: No columns found for table BLBDAYLIB/STT02P: table name is likely to be invalid.

Interestingly, using the same map but pointing to our test iSeries LPAR, everything works as expected.

I've attached two DataStage race files.. Live (with the error) and test (without).

As far as we can see DS is logging on the the iSeries but failing when compiling the native SQL complaining that it cannot find the column definitions.

Connection is via ODBC



Things we have tried.

1. Created a duplicate files on the iSeries in the same library from the offending object, changed the DS map to point to that file and re-tested.

2. as above but created the file from DDS re-compile.


We are pretty sure it's an ODBC driver problem.. but to add a spanner in the works.. we have another file in the same library using the same ODBC connection that get's updated around 4pm daily. This works fine.


iSeries version 5.4


Any help would be greatly appreciated.

[GLS400]

Re: Datastage - iSeries problem

Hi Huddy:
I know nothing about datastage but you are using 2 different file names

Table name: BLBDAYLIB/STT02P
Table name: BLBDAYLIB/STT02PTEST

Do both files have the same layout?
The error message seems to say that BLBDAYLIB/STT02P does not exist or is a flat file.

Best of Luck
GLS

[Scott M]

Re: Datastage - iSeries problem

Your live.txt is looking for a file STT02PTEST, maybe that is the issue. Could also be an authority issue on the file for the user profile you are connecting with.

[Huddy]

Re: Datastage - iSeries problem

Hi, thanks fior the early replies.

Yes they are the same file. The live one shown (STT02PTEST) is just a copy of STT02P because the only place we get the error is on live.. and we can't shut down the live feed intraday to test and diagnose. So we created a duplicate object as per opening post but get the error regardless.

As to object authorites .. Good shout. I should have mentioned that object authority is granted at library level so each object therein the library has *PUBLIC *ALL. Nothing has has changed on the iSeries on either system. Besides, the file that does work resides in the same library as the one that doesn't. Both have the same object authorities.

We use the same user profile for both maps, as you can see by the logs.

[MichaelCatalani]

Re: Datastage - iSeries problem

Table not accessible could also mean that the file has a lock on it, which will prevent the process from running.

If you're running under commitment control, double check the commit level required by the process.

There's also the possibility that the process is executing something which requires the file to be journaled, or even journaled with *BOTH images. I'm not sure what error that would throw.

It does feel more like an authority issue, which prevents the process from seeing the object altogether.

[tomliotta]

Re: Datastage - iSeries problem

Success happens with 'Datasource : ODBC_BLBUKTST' and fails with 'Datasource : ODBC_BLBUKPRD'. Can you post details of both data sources? Also, just for possible reference, can you attach DSPFFD output for the two files?

[Huddy]

Re: Datastage - iSeries problem

Yes of course. Thanks Tom. I'll do this tomorrow but in the meantime i'll answer the questions above.

1. The working file and non working file are both in the same library and both have the same object authorities of *public *all. We use library level authorities so it shouldn't be an issue.
2. Both files are journalled using image *both
3. Both the working and non working file use the same ODBC driver and connection. I'll post details tomorrow.

thanks

[tomliotta]

Re: Datastage - iSeries problem

I don't know anything about "Datastage". But it's not clear how the two can "use the same ... connection" yet have two separate data-sources. I don't understand what the "Datasource : ODBC_BLBUKPRD" lines are referring to in the logs that you posted. I assume that you mean that the two connections are both established through the same route between the same systems using the same credentials.

[Huddy]

Re: Datastage - iSeries problem

Correct.

Our Maps in Datastage all come from the same data source; Sybase using it's own internal adaptor and transferred to the iSeries using a single ODBC connection.

All maps these maps use the same ODBC connection to write to the iSeries and as as you rightly say, share the same same login credentials.

I'm collecting the information as i write.

[Huddy]

Re: Datastage - iSeries problem

Ok, I've attached the file descriptions for the said files.

Just to re-iterate.


The problem is with STT02P on our production LPAR only (BLBUKPRD) when upgraded our DS software. The transfer worked fine on the same file on our test LPAR (BLBUKTST) and during testing.


I can't perform tests on our live STT02P because that's constantly in use by the existing DS version. We have replicated the problem by copying the file to STT02PTEST in the same library with the same object authorities in the same library. That way we can leave our live data feed alone. If we can get this working, we'll know what the problem is.

In contrast, SUM04P does work with the new release of DS. SUM04P is in the same library as STT02P and STT02PTEST using the same ODBC connection and credentials.

All objects have *PUBLIC *ALL as the authrority is granted at library level.

All object have Journalling on with images *BOTH


As said before, the confusing part is why SUM04P works fine with the new release of DS but STT02P (STT02PTEST) doesn't because they are using the same ODBC connection, have the same authorites and reside in the same library.

I'm putting together some screen shots of the ODBC driver but i doubt this is the problem else SUM04P wouldn't work hence the headache of this dilemma.

[tomliotta]

Re: Datastage - iSeries problem

What is the result of running RCLDBXREF OPTION(*CHECK)? I wouldn't expect it to show any problem after the things you say you tried, but it should be eliminated as a potential problem area.

[Huddy]

Re: Datastage - iSeries problem

I'll try that now thanks for your help.

edit - excuse my ignorance, where is that command? On the compile options?

[tomliotta]

Re: Datastage - iSeries problem

RCLDBXREF is a system command that is related to the system database catalog. Under some circumstances, the database catalog can be in a condition that isn't fully synchronized with all of the database files on your system.

The command has a parm that can be OPTION(*CHECK) or OPTION(*FIX). The OPTION(*CHECK) will let you know if any unsynchronized state is detected, and OPTION(*FIX) will do what is possible to repair any differences. The command is similar to the RCLSTG SELECT(*DBXREF) when it is run with OPTION(*FIX). With OPTION(*CHECK), it's intended to be safe to run at any time. I wouldn't run it when significant functions such as library restores are being done just on general principles.

(But note that the command does not exist on older releases of the OS. If your system doesn't have the command, please post your OS version; but it seems to be V5R4.)

[Huddy]

Re: Datastage - iSeries problem

Thanks for that explanation tom.

Verified with no errors

[Scott M]

Re: Datastage - iSeries problem

It appears that there is something about the file in your production LPAR that is somehow different. And a copy of it still has the same problem. And you have already looked at everything obvious. So, how about doing a save of the file in your test LPAR and restoring it to your production LPAR under a different name such as STT02PTST2 and see what happens. If that worked ok then maybe you could at some point rename that file to STT02P after first copying the production data to it.