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

COBOL DB2 V8 Mainframe - moving data from one table to another

P: 3
Hello to all.

This is my first post. I have received many answers to questions in the past, but this one I cannot find.

We need to move data from production tables to archive tables. I wrote this:
[code]
EXEC SQL
INSERT INTO table1_archive
SELECT *
FROM table1_production
WHERE table1_nbr = :some number
END-EXEC. [code]

Which worked, while the tables were identical. Now a DATE column defined as “NOT NULL WITH DEFAULT has been added to the archive tables in the first column.

Now I am getting SQLCODE=-408 ( THE VALUE IS NOT COMPATIBLE WITH THE DATA TYPE OF ITS TARGET.)

Is there a way to skip this first column, or say to start at column 2?

Any ideas? we have lots of tables at this time to be archived, each having 1 to many rows.

I truly appreciate the knowledge and expertise that all of you bring to this forum. Thank you!
Jun 28 '07 #1
Share this Question
Share on Google+
6 Replies


10K+
P: 13,264
Hello to all.

This is my first post. I have received many answers to questions in the past, but this one I cannot find.

We need to move data from production tables to archive tables. I wrote this:
[code]
EXEC SQL
INSERT INTO table1_archive
SELECT *
FROM table1_production
WHERE table1_nbr = :some number
END-EXEC. [code]

Which worked, while the tables were identical. Now a DATE column defined as “NOT NULL WITH DEFAULT has been added to the archive tables in the first column.

Now I am getting SQLCODE=-408 ( THE VALUE IS NOT COMPATIBLE WITH THE DATA TYPE OF ITS TARGET.)

Is there a way to skip this first column, or say to start at column 2?

Any ideas? we have lots of tables at this time to be archived, each having 1 to many rows.

I truly appreciate the knowledge and expertise that all of you bring to this forum. Thank you!
Moved to DB forum.
P.S Welcome to TSDN
Jun 28 '07 #2

rickbray66
P: 7
You might try specifying the actual field names in your INSERT and SELECT statements:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO myarchive (field2, field3, field4)
  2. SELECT field1, field2, field3 FROM mytable;
  3.  

This worked for me in a sample test I just tried using the CLP.


Rick
Jun 28 '07 #3

P: 3
I am hoping to avoid listing all the column names. But thanks!
Jun 29 '07 #4

rickbray66
P: 7
I am hoping to avoid listing all the column names. But thanks!
I'm not sure you can easily do that. Perhaps somebody else can provide a better answer for you.

One thing you could do -- based on your initial statement that the new column in each table is a DATE or TIMESTAMP field, I'm guessing what you're looking for is adding the date that the table was archived. You could achieve the same effect by not adding the column until after the data has been moved into the archive table.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO myarchive SELECT * FROM mytable;
  2. ALTER TABLE ADD date_archived TIMESTAMP default CURRENT_TIMESTAMP;
  3.  

Rick
Jun 29 '07 #5

P: 3
This is going to be an ongoing process; therefore, altering the tables each time would not be a good solution. I appreciate all suggestions!
Jun 29 '07 #6

P: 24
I seem too remember (and I could be completely wrong!) that when you are not identifying the rows DB2 attempts to add the value in the first column on the input table to the first column on the output table. It does not do anything clever like checking the if the columns are named the same. So the error message indicates that the first column on the live table is not of a compatible type with the archive table - probably stating the obvious but then my suggestion below tends to make a bit more sense!

You may want to try putting the date field at the end of the archive table and setting it to so the default is null. As new rows are added the table all the rows from the live table will be loaded correctly into the archive table and the date field is set to null.

If the date field is meant to track when the archive rows were added to the table you can then run an UPDATE query after you have copied the new rows into the table to set the date field to CURRENT DATE where the current value is NULL.

Regards

Snib
Jun 30 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.