473,320 Members | 1,724 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQLCODE error when trying to access temporary table

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
4 12002
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

"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Debbie | last post by:
Hi I have developed an application that access an SQL database and installed it on a different machine The installation is successful but when I try to run the application I get the following...
1
by: Jeff Thur | last post by:
I am getting this error when trying to run a stored procedure: Microsoft.VisualBasic.CompilerServices.LateBinding.LateInd exGet(Object o, Object args, String paramnames) +1361...
1
by: Ghanashyam | last post by:
Dotnet Framework 1.1 is installed on my computer running on Windows 2000 Professional.Visual Studio >net 2003 is installed.When I try to create an ASP.NET project I get following error. The web...
5
by: snicks | last post by:
I'm trying to exec a program external to my ASP.NET app using the following code. The external app is a VB.NET application. Dim sPPTOut As String sPPTOut = MDEPDirStr + sID + ".ppt" Dim p As...
1
by: dipesh | last post by:
I am getting error when trying to Add Web Reference in Visual Studio 2005. "Some of the files required for web references are not installed"
2
by: ticars | last post by:
I'm getting a weird error when trying to access a user control from within a base page during runtime. Here's what I have: I have a master page with a user control on it. I then have a few...
3
by: LRI41 | last post by:
I googled an error message and it came up with two posts on your forum and when I got there it said I needed to register to post my question, I am not sure whether is this the correct place to...
5
by: Martin | last post by:
I get below error when trying to write unicode xml to a zipfile. zip.writestr('content.xml', content.toxml()) File "/usr/lib/python2.4/zipfile.py", line 460, in writestr zinfo.CRC =...
1
by: jonny | last post by:
Went from using Visual Web Develop express to Visual Studio 2005 and getting error when trying to open project. Error message: "One or more projects in the solution could not be loaded for the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.