use toad db2 to move table data from one table/database to another table/database … bypass export/scp/load
get many requests to copy data from one table/database to another table/database …
need to bypass bypass export/scp/load
HOW
Hello ronish,
Can you please describe what steps did you performed (one by one) and what is the operation you are not able to perform (and a screenshot with errors if present).
Thank you.
Regards
Ondrej
That’s no what im saying. The standard ways of moving table data via expscp/load or load from cursor are always there. Toad is a productivity tool. Using TOAD is there a fast way to
copy data from table to table with a few keystrokes? A huge chunk of what dbas do is move data around.
Asking if toad can help there
Asking if toad has a quick way of moving table data from db/table to db/table.
Hi Ronish;
Thank you for posting your question.
is this a mainframe or LUW that you want “move table data from db/table to db/table”?
Yahya
LUW
From: yahya.nattagh [mailto:bounce-yahyanattagh@toadworld.com]
Sent: Friday, January 26, 2018 10:54 AM
Subject: RE: [Toad for IBM DB2 - Discussion Forum] use toad db2 to move table data from one table/database to another table/database … bypass export/scp/load
Reply by yahya.nattagh
Hi Ronish;
Thank you for posting your question.
is this a mainframe or LUW that you want “move table data from db/table to db/table”?
Yahya
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for IBM DB2 Forum notifications altogether.
Toad for IBM DB2 - Discussion Forum
Flag
this post as spam/abuse.
Here is an example of the wizard.
Then you run it as a script.
Is this sufficient answer?
Regards
Ondrej
Generates these 2 scripts for copying 200 rows, data only.
Im an Oracle guy. The sequence is to run the export… create some file on my local PC. Then import that file. Not recreating the table just replacing data that is already there.
The data path
%DATA_PATH% How does that get filled in?
EXPORT TO ‘%DATA_PATH%\DOF_METRICS_FCT_REV_COLL_OCA_1955216642.ixf’
OF IXF
SELECT REV_COLL_OCA_UID, UNIT_UID, CAT_UID, CAT_SUB_UID, COLL_TYP_UID, VNDR_UID, CAL_UID_MNTH_END, REV_AMT, COMMENT, DT_FILE_RCVD, DT_LOAD, DT_MODIFIED FROM “DOF_METRICS”.“FCT_REV_COLL_OCA”;
IMPORT FROM ‘%DATA_PATH%\DOF_METRICS_FCT_REV_COLL_OCA_1955216642.ixf’
OF IXF
MODIFIED BY COMPOUND=5
NOCHECKLENGTHS
METHOD N
(REV_COLL_OCA_UID,
UNIT_UID,
CAT_UID,
CAT_SUB_UID,
COLL_TYP_UID,
VNDR_UID,
CAL_UID_MNTH_END,
REV_AMT,
COMMENT,
DT_FILE_RCVD,
DT_LOAD,
DT_MODIFIED)
COMMITCOUNT 1000
INSERT INTO DOF_METRICS.FCT_REV_COLL_OCA
(REV_COLL_OCA_UID,
UNIT_UID,
CAT_UID,
CAT_SUB_UID,
COLL_TYP_UID,
VNDR_UID,
CAL_UID_MNTH_END,
REV_AMT,
COMMENT,
DT_FILE_RCVD,
DT_LOAD,
DT_MODIFIED);
The wizard creates an export/import pair. Run export then import. The symbolic %DATA_PATH% how does that get filled in?
This is a dataonly move.
That symbolic gets resolved via the value at Tools - options...
Database - DB2 - LUW - General Utilities - Utility File Directory
The wizard creates 2 files: An export file An import file. The export on the source, the import on the target. Both are clp commands prefixed by db2.
Is this a manual process where you connect to the source and cut past the edit output prefixed by db2 and create the ixf file on your local pc.
Then do the same on the target connect to the target and cut past the edit output prefixed by db2 and use the ixf file on your local pc to copy the date?
If this is whats done, where is the savings?
The wizard creates 2 files: An export file An import file. The export on the source, the import on the target. Both are clp commands prefixed by db2.
Is this a manual process where you connect to the source and cut past the edit output prefixed by db2 and create the ixf file on your local pc.
Then do the same on the target connect to the target and cut past the edit output prefixed by db2 and use the ixf file on your local pc to copy the datA?
If this is whats done, where is the savings?
The WIZARD CREATES 2 SCRIPTS. THEN YOU MANUALLY HAVE TO RUN THESE SCRIPTS. HOW DO YOU RUN THESE SCRIPTS UNDER TOAD
ONCE THEY ARE CREATED.
The 2 scripts that get produced, the export and import. How do they get invoked in TOAD? They have to be fed to DB2’s CLP. How is that done. A manual process or is
there an automated way of feeding the two scripts.
The screens are clear. The output that comes out is not so clear.
The export which includes symbolics how does it actually get run. It needs to run under CLP. Do you have to create a script to have it run under TOAD?
Like wise the import which includes sql to drop recreate the table the CLP run the import. Need to create a second script for that to work?
Hello,
can you please upload here scripts which were generated? Not copy & paste to the forum, but ASCI files as an attachment.
Regards
Ondrej
First part
EXPORT TO
‘%DATA_PATH%\PASS_STAFF_CHANGE_LOG_1994049310.ixf’
OF IXF
SELECT STAFF_HISTORY_LOG_ID, STAFF_UID, STAFF_ATTRIBUTE_CD, STAFF_ATTRIBUTE_VALUE, EFFECTIVE_BEGIN_DT, EFFECTIVE_END_DT, CURRENT_IND FROM "PASS"."STAFF_CHANGE_LOG";
Second part
– Export of data for migrate should be executed before
– Step 1. Export of data for preserve
CREATE
TABLE TMP_CRTNW.TMP_PASS_STAFF_CHANGE_LOG_SHYHB(STAFF_HISTORY_LOG_ID DECIMAL(18, 0), STAFF_UID INTEGER, STAFF_ATTRIBUTE_CD SMALLINT, STAFF_ATTRIBUTE_VALUE VARCHAR(20), EFFECTIVE_BEGIN_DT TIMESTAMP, EFFECTIVE_END_DT TIMESTAMP, CURRENT_IND CHARACTER(1)) IN TSSL04KPS_001;
INSERT
INTO TMP_CRTNW.TMP_PASS_STAFF_CHANGE_LOG_SHYHB SELECT STAFF_HISTORY_LOG_ID, STAFF_UID, STAFF_ATTRIBUTE_CD, STAFF_ATTRIBUTE_VALUE, EFFECTIVE_BEGIN_DT, EFFECTIVE_END_DT, CURRENT_IND FROM PASS.STAFF_CHANGE_LOG;
– Step 2. Drop
DROP
TABLE PASS.STAFF_CHANGE_LOG;
– Step 3. Sync
CREATE
TABLE PASS.STAFF_CHANGE_LOG (
STAFF_HISTORY_LOG_ID DECIMAL(18, 0) NOT
NULL,
STAFF_UID INTEGER NOT
NULL,
STAFF_ATTRIBUTE_CD SMALLINT NOT
NULL,
STAFF_ATTRIBUTE_VALUE VARCHAR(20),
EFFECTIVE_BEGIN_DT TIMESTAMP NOT
NULL,
EFFECTIVE_END_DT TIMESTAMP NOT
NULL,
CURRENT_IND CHARACTER(1)
)
IN
TSSL04KPS_001;
ALTER
TABLE PASS.STAFF_CHANGE_LOG
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
SET
SCHEMA
= RPAP1AII;
GRANT
SELECT
ON
TABLE PASS.STAFF_CHANGE_LOG TO
GROUP BORPT;
SET
SCHEMA
= SCHLIMEA;
GRANT
INSERT, UPDATE
ON
TABLE PASS.STAFF_CHANGE_LOG TO
GROUP BORPT;
SET
SCHEMA
= RPAP1AII;
GRANT
ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE
ON
TABLE PASS.STAFF_CHANGE_LOG TO
GROUP DSSYUSR;
GRANT
SELECT
ON
TABLE PASS.STAFF_CHANGE_LOG TO
GROUP DSTAGE;
GRANT
DELETE, INSERT, UPDATE
ON
TABLE PASS.STAFF_CHANGE_LOG TO
GROUP PASS;
SET
SCHEMA
= RPAT1AII;
GRANT
SELECT
ON
TABLE PASS.STAFF_CHANGE_LOG TO
GROUP PASS;
SET
SCHEMA
= RPAP1AII;
GRANT
DELETE, INSERT, UPDATE
ON
TABLE PASS.STAFF_CHANGE_LOG TO
GROUP PASSUSER;
SET
SCHEMA
= RPAT1AII;
GRANT
SELECT
ON
TABLE PASS.STAFF_CHANGE_LOG TO
GROUP PASSUSER;
SET
SCHEMA
= RPAP1AII;
Hello,
If you want just replace the data, use the first par to export data and step 5 (except DROP SCHEMA) from the second part to import and replace data. You can execute both commands in Editor in Toad for DB2.
Regards
Ondrej
That clears it. Its a manual process. The one part that I still dont get. export is a clp command. That means you preface commands with
db2 "export. Yet in the editor you omit the db2 and it works . Can you explain that!
If you just want to move the data and not create the target table - it needs to be there in the same format as the source table.
You can de-select all the DDL check boxes and select the Data check box for the table as shown below:
Then on the final page of the Migration Wizard - click 'Send to Editor'.
Then run the export script first - you can run the generated scripts in Toad's SQL Editor as is:
Then the import script:
Our SQL Editor is intelligent enough to understand and run commands (i.e, EXPORT, IMPORT, LOAD, RUNSTATS).
It parses statements to understand what they are and then will invoke the CLP if needed to run true DB2 commands.
Starting to make some sense. Feed the edited output straight to the editor and let it figure out the location of the ixf/del files.
One quickie, as far as export/import goes does it matter or is there any difference between “ixf” vs “del” files. Clear that if the target ddl is not there that ixf will recreate
On a straight replace you use ixf but would it matter if the type were del. The reason i dwell on this is I remember once i backed it up with ixf and restored with ixf. It wouldnt take.
went to del did the same thing and it took.