DB2 problem, Invalid string or buffer length. SQLSTATE=HY090

[supernova2005]

Hi everyone, I posted this message earlier on the PX forum, but I built the same job in Server and the same error message appears.


This error message appears on some of my DB2 API stages, and if I replace the DB2 stage with a DRS stage, the error message disappeared. Does anyone know what's going on?

here is the error message:


APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM][CLI Driver] CLI0131E Invalid string or buffer length. SQLSTATE=HY090
SQLTables: Error retrieving system catalog information for requested tables(s).



The Transaction Isolation of the DB2 stage is "Repeatable Read",
The Buffering mode is "Default", that is Maximum memory buffer size =3145728, Buffer free un(percent)=50, Queue upper bound size = 0, Disk write increment = 1048576

Could the maximum memory buffer size be a problem?
I also notice that some tables have an aliase, could the aliase be a problem?

[gpatton]

What version of DB2 are you using?

[ray.wurlod]

Do you have SELECT privilege granted on the requisite system tables?

[supernova2005]

yes. If I don't have the select privilege, the DRS stage won't work. I can even the view table on the DB2 stage. This problem only happens when I run the job.

Do you have SELECT privilege granted on the requisite system tables?

[peterbaun]

We had a similar problem a while ago on db2/as400 although the error message was not entirely the same.


Have a look at this Ecase/Article - G56095 :

Topic:
Possible unessecary call with db2 cli stage
Product: DataStage
Release: 7.0.1
Date Entered: 06/29/2004
Fixed in Release: 7.1r2,7.5X2,7.5.1
Date Closed: 08/13/2004

Full Description:
Different calls seem to be made for a view data, and a run of the same job.
The job simply extracts data from ANY db2 table and writes to a flat file.
The run will contain the following line in the trace
[1087929954.155916 - 06-22-2004 14:45:54.155916] (
StmtOut="CALL SYSIBM.SQLTABLES(?,?,?,?,?)" )

The view data trace will not contain this line.

The problem is that this call is taking an excessive amount of time on the customers system.
Why does view data, which returns all the data in a small table, not make the call, but a job run
that reads the same table make the call? It seems that the call is not needed.

In order to turn on tracing put the following lines in the db2cli.ini file in
INSTHOME/sqllib/cfg

[COMMON]
trace=0
tracepathname=/tmp
tracecomm=1
tracetimestamp=1

Any DB2cli stage to a flat file will reproduce this issue.


On DB2/AS400 it was a problem with the release level of the database. It never worked on release 4 and lower. For release 5R1 a PTF needed to be applied which created the necessary system tabels (actually they were created as views or aliases). I also discovered that using the DRS stage worked.

Turning on the trace should give you more exact details about what actually is failing.

Hope it helps

Regards
Peter

[Titto]

Same thing happenning with me also, Source is AS400 - DB2.

I used User defined SQL to join 3 tables to get the data, when view the data it is perfectly retrieving the data but when I run the job it is aborting with
"[IBM][CLI Driver] CLI0131E Invalid string or buffer length. SQLSTATE=HY090
SQLTables: Error retrieving system catalog information for requested tables(s). " Error.

It is happenning with only User defined SQL's, not with Generated SQL. But i guess using Generated SQL we can't join table using one DB2 Stage.

Any help is appreciated!

Thanks!

[ray.wurlod]

If I don't have the select privilege, the DRS stage won't work.

I asked whether you had SELECT privilege to the system tables, not to the data tables.
It is the system tables that must be interrogated to return a list of table names, where it appears your problem is occurring.