Hello all,

I'm having trouble when pulling data from a Z/OS db2 table to a db2 table on AIX. I get the sql -30080 error when trying to pull across more than 63 of the 97 columns I need.
It doesn't seem to matter what data type the columns are as long as I don't attempt to pull more than 63 columns over, it works. I found the problem while trying to load/replace the data into the db2 table on AIX, but the behavior is the same when running a simple select statement.

Any assistance on this would be much appreciated.

Thanks

What do you mean by "pulling". What is the exact tool and command you are using? Please also copy and paste the exact error message you get.

When pulling doesn't work, try pushing instead.

By "pulling" I mean selecting rows of data from the db2 table on the mainframe and load/replacing that data into a db2 table in our AIX environment.

This is the error I receive when executing a select statement that will select data from more than 63 columns from the db2 table on the mainframe.

SQLCA Information

sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1476 sqlerrml: 6
sqlerrmc: -30080
sqlerrp : SQLRR0F7
sqlerrd : (1) -2145779603 (2) 0 (3) 0
(4) 0 (5) -200 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 40506

You did not explain what tool or interface you used to extract the data from the DB2 z/OS table.

Marcus,

I'm executing the select statement from the command line. Something like:
db2 -v "select col1, col2, ...col63 from src.host_table where col1_value = 'X' "

The src.host_table is a db2 nickname pointing the table on Z/OS.

"Something like" may not be precise enough. Are you using a DISTINCT, a UNION, or an ORDER BY there, by chance? What is the total length of columns col1 through col63?

I apologize for the lack of precision. I have "FETCH FIRST 2 ROWS ONLY WITH UR" at the end of the statement to ensure I don't lock the table while selecting from it and I just want a sample of the data, so fetch first 2 rows keeps me from filling up a log with 1.2 million rows at 404 byes long each.

The select statement is as basic as it can get. I wrote the select statement with only the columns that were a CHAR data type and the total row length was 215 bytes. There are 62 columns with a CHAR data type and when I executed the query, it returned data values for all 62 columns. I added 2 more columns of that were SMALLINT data types and got the -30080 error.

All 97 columns create a 404 byte row length. I believe there are a little more than 1.2 million rows on the source table, but as I said before, I'm asking for only the first 2.

The first time I saw this error was while attempting to load ALL rows from the source table into my table. To do that, I declare a cursor with:
"DECLARE load_cur CURSOR FOR ", then the select statement. The next statement is the load statement:
"LOAD FROM load_cur OF CURSOR". But I can't get a return from a basic SELECT statement, so I know the load won't work.

Do you know what versions of DB2 you are using on both ends? Also, it might be a good idea to check that the nickname catalog information on AIX is in sync with the remote object on z/OS.

Are data types of all 62 columns CHAR? Not VARCHAR?
I want to see CREATE TABLE(on z/OS)/CREATE NICKNAME(on AIX) statements(you can change column names and table names) and complete and exact(copy/paste) SELECT statement(you can change column names and table names after copy/paste using same changed names for CREATE statements).
Your samle include "select col1, ..." and "where col1_value".
If you changed column names, you should keep one by one correspondance between original column names and changed column names.

We are running db2 v9.5 on both ends. I actually just connected directly to the db2 table on zOS and issued the select statement. It failed with the same error.

Is it a physical table or a view?

By the way, there is no DB2 9.5 for z/OS

Ok, so the z OS version is 9.

I'm selecting from a table on the z side and loading into a table in my environment using a nickname. I have connected directly to the table on z and it's the same result, -30080.

If I were seeing this error when selecting 1 column I could understand looking at the versions or object differences, but I get a return from the select up to a point. I don't know if there is an operating system config that might be causing a problem or what. We are running AIX 5.3.0.0.

You are being very impercise on your descriptions of what you are doing. When you say "connected directly to the table on z" does that mean you logged on to TSO and used SPUFI? Or did you select the the data from a remote UNIX or Windows command line using DB2 Connect?

Have you considered opening a problem with IBM support?