-906 on SAS/DB2 job

Stephen Mallett

-906 on SAS/DB2 job
to all DB2 Listers who are familiar with SAS:

We’re getting an SQL Code of –906 from a Batch SAS job and I suspect the
SAS code is missing a RUN (or perhaps an explicit COMMIT or DISCONNECT)
after the previous SAS step but I don’t know SAS all that well.
The previous step deletes all rows from a work table and re-populates it
with 3069 INSERTs using a SAS Macro. (DB2 LOAD isn't really needed as each
row has only the single CHAR(8) NotNULL column).

Could I get some/any advice from any of the SAS DB2 gurus on the list?


thanks,
Steve
OS390 DB2 V6 SAS 8.2

PS I’m on the road so it may take a little while to respond to any
questions
----


The explanation for the DB2 error is

-906 THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE THIS FUNCTION
IS
DISABLED DUE TO A PRIOR
ERROR


Explanation: Execution of the SQL statement failed because the
requested
function had been disabled by a prior error. This situation can arise
if
the application program has intercepted an abend (for instance, by an
ON
ERROR condition in a PL/I program) and continued to execute
SQL
statements. This situation may also arise if a DB2 CICS
transaction
encountered a create thread error yet continued to issue SQL
requests
without issuing a SYNCPOINT ROLLBACK
first.


- - -

The SAS LOG looks something like the following
NOTE: Copyright (c) 1999-2001 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software Release 8.2 (TS2M0)
Licensed to USHERE, Site 0099999001.
NOTE: This session is executing on the OS/390 V02R10M00 platform.

NOTE: Running on IBM Model 9672 Serial Number 099999,
IBM Model 9672 Serial Number 199999.

. . . .
1698
1699 libname aism db2
SYMBOLGEN: Macro variable MDBSSID resolves to DB2T
1699 ! authid=aism ssid=&mdbssid schema=aism;
NOTE: Libref AISM was successfully assigned as follows:
Engine: DB2
Physical Name: DB2T
1700
1701 proc sql;
1702 connect to db2
1702 ! (ssid=&mdbssid);
SYMBOLGEN: Macro variable MDBSSID resolves to DB2T
1703
1704 delete from aism.ait_project_emrs;
NOTE: 3069 rows were deleted from AISM.AIT_PROJECT_EMRS.

1705 %sqlchk;
SYMBOLGEN: Macro variable SQLXRC resolves to 0
0
SYMBOLGEN: Macro variable SQLXMSG resolves to

1706
1707 options nonotes;
1706

1707 options
nonotes;
1708

1709 %macro
insproj;
1710 %do i=1 %to
&maxobs;
1711 insert into aism.ait_project_emrs(nproject) values
(&&p&i);
1712 %
end;
1713 %
sqlchk;
1714 disconnect from
db2;
1715 %
sqlchk;
1716 %mend
insproj;
1717

1718 %
insproj;
SYMBOLGEN: Macro variable MAXOBS resolves to 3069
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable P1 resolves to 'AAG80000'
. . . couple of thousand similar lines . . .
SYMBOLGEN: Macro variable P3068 resolves
to 'ZST19020'
SYMBOLGEN: && resolves to
&.
SYMBOLGEN: Macro variable I resolves to
3069
SYMBOLGEN: Macro variable P3069 resolves
to 'ZST71010'
SYMBOLGEN: Macro variable SQLXRC resolves to
0
0

SYMBOLGEN: Macro variable SQLXMSG resolves
to


SYMBOLGEN: Macro variable SQLXRC resolves to
0
0

SYMBOLGEN: Macro variable SQLXMSG resolves
to


1719

1720 options
notes;
1721

NOTE: The PROCEDURE SQL used 11.23 CPU seconds and
12427K.


1722 PROC
SQL;
1723 /* SQL to select code table values and the project
details
1724 to be reported
*/
1725 CONNECT TO
DB2
1725 !
(SSID=&MDBSSID);
SYMBOLGEN: Macro variable MDBSSID resolves to
DB2T
1726 CREATE table
SASDATA.CDV
1727 /* (TYPECOD, ARGCOD, VALDESC)
*/
1728 AS SELECT * FROM CONNECTION TO
DB2
1729 (SELECT .C_TABLE_TYPE as typecod,
1730 C_TABLE_ARG as argcod,
1731 T_TABLE_VAL as valdesc
1732 FROM MYDB.MYT_CODE_TABLE
. . . .
1762 );

ERROR: DB2 prepare
error: .
ERROR: DB2 prepare
error: .
ERROR: DB2 prepare
error: .
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.

1763 %
SQLCHK;
SYMBOLGEN: Macro variable SQLXRC resolves to -
906
-
906

SYMBOLGEN: Macro variable SQLXMSG resolves
to


1764



Michael Ebert

Re: -906 on SAS/DB2 job
(in response to Stephen Mallett)
Hi Stephen,

you've got a syntax error in your second PROC SQL:

1728 AS SELECT * FROM CONNECTION TO DB2
1729 (SELECT .C_TABLE_TYPE as typecod,
1730 C_TABLE_ARG as argcod,
1731 T_TABLE_VAL as valdesc
1732 FROM MYDB.MYT_CODE_TABLE
. . . .
1762 );

There's an unwanted period in front of the C_TABLE_TYPE, causing the
PREPARE error and subsequent -906.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



to all DB2 Listers who are familiar with SAS:

We're getting an SQL Code of ?906 from a Batch SAS job and I suspect the
SAS code is missing a RUN (or perhaps an explicit COMMIT or DISCONNECT)
after the previous SAS step but I don't know SAS all that well.
The previous step deletes all rows from a work table and re-populates it
with 3069 INSERTs using a SAS Macro. (DB2 LOAD isn't really needed as each
row has only the single CHAR(8) NotNULL column).

Could I get some/any advice from any of the SAS DB2 gurus on the list?


thanks,
Steve
OS390 DB2 V6 SAS 8.2

PS I'm on the road so it may take a little while to respond to any
questions