By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,226 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,226 IT Pros & Developers. It's quick & easy.

SQLCODE error when trying to access temporary table

P: n/a
I am getting sql error during binding a program which access a temporary table.
The temporary table declaration and access methods are given below.
EXEC SQL
DECLARE GLOBAL TEMPORARY TABLE TEM88
LIKE SYSIBM.SYSDUMMY1
END-EXEC.
EXEC SQL
INSERT INTO TEM88
SELECT * FROM SYSIBM.SYSDUMMY1
END-EXEC.
For the above sql, SQLCODE -408 during bind.
EXEC SQL
SELECT IBMREQD INTO :TEM88-IBMREQD FROM TEM88
END-EXEC.
For the above sql, SQLCODE -206 during bind.
DB2 SQL PRECOMPILER VERSION 7 REL. 1.0
DB2 Version: 7.1.1
Platform MVS
Any clues as to what is happening ?
Thanks in advance.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
I'm not sure about MVS but on LUW; SYSIBM.SYSDUMMY1 IS NOT A TABLE.

SYSIBM.SYSDUMMY1 can be referenced in a SELECT to get a single row
result but there is no definition for the table in the catalog.
SYSIBM.SYSDUMMY1 does NOT contain columns so the DECLARE, while
apparently working, yields a table that cannot contain data. This would
account for the insert sql bind failures.

I duplicated your statements on LUW and found that the DECLARE worked
but I couldn't insert into it. The following SQL, which works fine when
selecting from SYSIBM.SYSDUMMY1 also failed - as expected:
SELECT CURRENT USER FROM SESSION.TEMP8

Phil Sherman

prasad wrote:
I am getting sql error during binding a program which access a temporary table.
The temporary table declaration and access methods are given below.
EXEC SQL
DECLARE GLOBAL TEMPORARY TABLE TEM88
LIKE SYSIBM.SYSDUMMY1
END-EXEC.
EXEC SQL
INSERT INTO TEM88
SELECT * FROM SYSIBM.SYSDUMMY1
END-EXEC.
For the above sql, SQLCODE -408 during bind.
EXEC SQL
SELECT IBMREQD INTO :TEM88-IBMREQD FROM TEM88
END-EXEC.
For the above sql, SQLCODE -206 during bind.
DB2 SQL PRECOMPILER VERSION 7 REL. 1.0
DB2 Version: 7.1.1
Platform MVS
Any clues as to what is happening ?
Thanks in advance.


Nov 12 '05 #2

P: n/a

"Philip Sherman" <ps******@ameritech.net> wrote in message
news:Vq******************@newssvr17.news.prodigy.c om...
I'm not sure about MVS but on LUW; SYSIBM.SYSDUMMY1 IS NOT A TABLE.

I can't speak for V8.x of DB2 for Linux/Unix/Windows but in V7.2 of LUW,
Appendix D (Catalog Views) of the SQL Reference index lists SYSIBM.SYSDUMMY1
at the very start of the list of Catalog Views. According to the actual
article referenced by the link:

----
SYSIBM.SYSDUMMY1
Contains one row. This view is available for applications that require
compatibility with DB2 Universal Database for OS/390.


Table 41. SYSCAT.DUMMY1 Catalog View
Column Name Data Type Nullable Description
IBMREQD CHAR(1)
Y
----

When I look at the Sample database in the Control Center, SYSIBM.SYSDUMMY1
is *not* visible in the Tables list even the other SYSIBM.* tables are.
Also, SYSCAT.DUMMY1 is *not* visible in the Views list either.

However, when I do a SELECT * on SYSIBM.SYSDUMMY1 in the Command Center, the
query works fine and shows the single row in the table. When I do a SELECT *
on SYSCAT.DUMMY1, I get an SQL0204N error that says "SYSCAT.DUMMY1" is an
undefined name.

The documentation, the Control Center, and the Command Center are not very
consistent with respect to this table, I must say!

Rhino

Nov 12 '05 #3

P: n/a
Philip Sherman wrote:
I'm not sure about MVS but on LUW; SYSIBM.SYSDUMMY1 IS NOT A TABLE.
SYSIBM.SYSDUMMY1 is a view on LUW. And each view is nothing else than a
table. It has rows and columns. SYSIBM.SYSDUMMY1 has 1 row and 1 column -
but it is not a base table.
SYSIBM.SYSDUMMY1 can be referenced in a SELECT to get a single row
result but there is no definition for the table in the catalog.
SYSIBM.SYSDUMMY1 does NOT contain columns so the DECLARE, while
apparently working, yields a table that cannot contain data.
Your statement is not correct. The view has one column named IBMREQD of
type CHAR(1).
This would
account for the insert sql bind failures.

I duplicated your statements on LUW and found that the DECLARE worked
but I couldn't insert into it.
It works for me on the command line:

$ db2 "declare global temporary table temp8 like sysibm.sysdummy1 on commit
preserve rows not logged"
DB20000I The SQL command completed successfully.
$ db2 "describe select * from session.temp8"

SQLDA Information

sqldaid : SQLDA sqldabc: 896 sqln: 20 sqld: 1

Column Information

sqltype sqllen sqlname.data sqlname.length
---------------- ------ ------------------------- --------------
452 CHARACTER 1 IBMREQD 7

$ db2 "insert into session.temp8 select * from sysibm.sysdummy1"
DB20000I The SQL command completed successfully.
$ db2 "select * from session.temp8"

IBMREQD
-------
Y

1 record(s) selected.
The following SQL, which works fine when
selecting from SYSIBM.SYSDUMMY1 also failed - as expected:
SELECT CURRENT USER FROM SESSION.TEMP8


$ db2 "select current user from session.temp8"

1
----------------------------------------------------
STOLZE

1 record(s) selected.
If and how this might relate to MVS, I do not know.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4

P: n/a
Hello,

in DB2 for z/OS static SQL programs like your's, a declared temporary table
MUST always be referenced with a full qualified name using the literal
"SESSION" as creator/schema name. Otherwise, the bind process cannot realize
that you reference a DECLARED temporary table, since it does not exist
anywhere at bind time. Regarding your sample, I suspect furthermore that you
already created either a CREATED temporary table (which is permanently
recorded in the DB2 catalog) or an ordinary table with name "TEMP88" and
within the same schema your package gets bount into, but with different
column definitions as SYSIBM.SYSDUMMY1 - just to get rid of the
SQLCODES -204 during bind...

EXEC SQL

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEM88

LIKE SYSIBM.SYSDUMMY1

END-EXEC.

EXEC SQL

INSERT INTO SESSION.TEM88

SELECT * FROM SYSIBM.SYSDUMMY1

END-EXEC.

EXEC SQL

SELECT IBMREQD INTO :TEM88-IBMREQD FROM SESSION.TEM88

END-EXEC.

should work fine at runtime and avoid any warnings or errors during
precompile and bind!

Cheers - Walter SCHNEIDER.

"prasad" <kb**@rediffmail.com> schrieb im Newsbeitrag
news:8d**************************@posting.google.c om...
I am getting sql error during binding a program which access a temporary table. The temporary table declaration and access methods are given below.
EXEC SQL
DECLARE GLOBAL TEMPORARY TABLE TEM88
LIKE SYSIBM.SYSDUMMY1
END-EXEC.
EXEC SQL
INSERT INTO TEM88
SELECT * FROM SYSIBM.SYSDUMMY1
END-EXEC.
For the above sql, SQLCODE -408 during bind.
EXEC SQL
SELECT IBMREQD INTO :TEM88-IBMREQD FROM TEM88
END-EXEC.
For the above sql, SQLCODE -206 during bind.
DB2 SQL PRECOMPILER VERSION 7 REL. 1.0
DB2 Version: 7.1.1
Platform MVS
Any clues as to what is happening ?
Thanks in advance.

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.