7.2 to 8.1 DB2 migration problems

[Dmitry Bond]

Hi All.

After moving to new version of DB2 we faced with strange problem - code that
worked fine with DB2 v7.2.x on DB2 v 8.1 sometimes write error message like
the:

===message===
SQLCODE : -804
SQL0804N The application program parameters for the
current request are not valid. Reason code "107". If a
host variable or SQLVAR in the SQLDA is invalid then: host
variable/SQLVAR number = "6", SQLTYPE = "481", SQLLEN = "8",
host variable/SQLVAR type = "OUTPUT". SQLSTATE=07002
===message===

That code (written on C) is responsible for executing dynamic SQLs.
In general it looks like the:

EXEC SQL PREPARE sql_szExecStmt FROM :sql_szSqlStmt;
// [...]
EXEC SQL DESCRIBE sql_szExecStmt INTO :*SqlDaOut;
// [...]
EXEC SQL DECLARE sql_szExecCursor CURSOR FOR sql_szExecStmt;
// [...]
EXEC SQL OPEN sql_szExecCursor;
// line below is the source of mentioned error ...
EXEC SQL FETCH sql_szExecCursor USING DESCRIPTOR :*SqlDaOut;
// [...]
EXEC SQL CLOSE sql_szExecCursor;

Could you please share some of your experience - what could be the reason of
this error?
Why it worked find on previous version of DB2?
How we can fix it?

With regards,
Dmitry.

[Serge Rielau]

Dmitry Bond wrote:

Hi All.

After moving to new version of DB2 we faced with strange problem - code that
worked fine with DB2 v7.2.x on DB2 v 8.1 sometimes write error message like
the:

===message===
SQLCODE : -804
SQL0804N The application program parameters for the
current request are not valid. Reason code "107". If a
host variable or SQLVAR in the SQLDA is invalid then: host
variable/SQLVAR number = "6", SQLTYPE = "481", SQLLEN = "8",
host variable/SQLVAR type = "OUTPUT". SQLSTATE=07002
===message===

That code (written on C) is responsible for executing dynamic SQLs.
In general it looks like the:

EXEC SQL PREPARE sql_szExecStmt FROM :sql_szSqlStmt;
// [...]
EXEC SQL DESCRIBE sql_szExecStmt INTO :*SqlDaOut;
// [...]
EXEC SQL DECLARE sql_szExecCursor CURSOR FOR sql_szExecStmt;
// [...]
EXEC SQL OPEN sql_szExecCursor;
// line below is the source of mentioned error ...
EXEC SQL FETCH sql_szExecCursor USING DESCRIPTOR :*SqlDaOut;
// [...]
EXEC SQL CLOSE sql_szExecCursor;

Could you please share some of your experience - what could be the reason of
this error?
Why it worked find on previous version of DB2?
How we can fix it?

With regards,
Dmitry.

Well it seems to think that your output SQLDA for variable #6 is not set
up properly.
There has been major work in DRDA for V8. It could be that DB2 checks
for a field you coule get away with in the past.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[PM \(pm3iinc-nospam\)]

http://publib.boulder.ibm.com/infoce...30%34%4e%22%20
Version 8 incompatibilities with previous releases
Change
If a host variable is not declared in the BEGIN DECLARE section and is used
in the EXEC SQL section, SQL0306N will not be returned by the precompiler.
If the variable is declared elsewhere in the application, application
runtime will return SQL0804N. If the variable is not declared anywhere in
the application, the compiler will return an error at compilation time.

Symptom
Applications coded to react to an SQL0306N error at precompilation time may
not behave as before.

Resolution
Host variables should be declared in the BEGIN DECLARE section. If host
variables are declared in a section other than the BEGIN DECLARE section,
you should recode your application to handle SQL0804 return codes.

Does the shoe fit?

PM

"Dmitry Bond" <di******@mail.ru> a écrit dans le message de
news:10***************@moxa.united.net.ua...

Hi All.

After moving to new version of DB2 we faced with strange problem - code that worked fine with DB2 v7.2.x on DB2 v 8.1 sometimes write error message like the:

===message===
SQLCODE : -804
SQL0804N The application program parameters for the
current request are not valid. Reason code "107". If a
host variable or SQLVAR in the SQLDA is invalid then: host
variable/SQLVAR number = "6", SQLTYPE = "481", SQLLEN = "8",
host variable/SQLVAR type = "OUTPUT". SQLSTATE=07002
===message===

That code (written on C) is responsible for executing dynamic SQLs.
In general it looks like the:

EXEC SQL PREPARE sql_szExecStmt FROM :sql_szSqlStmt;
// [...]
EXEC SQL DESCRIBE sql_szExecStmt INTO :*SqlDaOut;
// [...]
EXEC SQL DECLARE sql_szExecCursor CURSOR FOR sql_szExecStmt;
// [...]
EXEC SQL OPEN sql_szExecCursor;
// line below is the source of mentioned error ...
EXEC SQL FETCH sql_szExecCursor USING DESCRIPTOR :*SqlDaOut;
// [...]
EXEC SQL CLOSE sql_szExecCursor;

Could you please share some of your experience - what could be the reason of this error?
Why it worked find on previous version of DB2?
How we can fix it?

With regards,
Dmitry.

[Dmitry Bond]

Hello.

Thank you (and Serge Rielau too) for answers. :-)

I found where problem was.
There was not initialized Null-indicator pointer (I mean -
SqlDaOut->sqlvar[i].sqlind, there was zero).
The columns 2 and 6 (specified in the error message) is the exactly columns
where the NULL value could occurs.
After I fixed this the problem disappeared. Seems it working fine now.

With regards,
Dmitry.

"PM (pm3iinc-nospam)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message
news:X9*******************@news20.bellglobal.com.. .

http://publib.boulder.ibm.com/infoce...30%34%4e%22%20 Version 8 incompatibilities with previous releases
Change
If a host variable is not declared in the BEGIN DECLARE section and is used in the EXEC SQL section, SQL0306N will not be returned by the precompiler.
If the variable is declared elsewhere in the application, application
runtime will return SQL0804N. If the variable is not declared anywhere in
the application, the compiler will return an error at compilation time. [...] Does the shoe fit?

PM