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

[Rainbow]

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!

[r035198x]

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

[rickbray66]

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

INSERT INTO myarchive (field2, field3, field4)
SELECT field1, field2, field3 FROM mytable;
 

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


Rick

[Rainbow]

I am hoping to avoid listing all the column names. But thanks!

[rickbray66]

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.

INSERT INTO myarchive SELECT * FROM mytable;
ALTER TABLE ADD date_archived TIMESTAMP default CURRENT_TIMESTAMP;
 

Rick

[Rainbow]

This is going to be an ongoing process; therefore, altering the tables each time would not be a good solution. I appreciate all suggestions!

[Snib]

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