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

Multitable insert in DB2 v8 os no Merge is avilable:

P: n/a
Hi everybody:
What is the best way to
I have 10 tables with similar INSERT requiremnts.
INSERT INTO ACSB.VAATAFAE
WITH AA(AA_TIN, AA_FILE_SOURCE_CD, .AA_TIN_TYP) AS
( SELECT AA_TIN, AA_FILE_SOURCE_CD, .AA_TIN_TYP
FROM VAATAFAA
WHERE AB_TP_ACNT_STAT_CD <0),

AE(ACS_TIN, ACS_FILE_SOURCE_CD, ACS_TIN_TYP………….) AS

(SELECT * FROM ACSB.VAATAFAE
INNER JOIN AA
ON AA .AA_FILE_SOURCE_CD = ACS_FILE_SOURCE_CD
AND
AA AA_TIN_TYP**** = ACS_TIN_TYP
AND
AA. AA_FILE_SOURCE_CD = ACS_FILE_SOURCE_CD)
SELECT * FOM AE;
Table VATATFAA HAS 10 MILS ROWS. AFTER SELECT IN TEMP AA ONLY 50k ROWS LEFT.
I insert IN ACSB.VAATAFAE ONLY MAYCHES WITH AA, but i have to insert in 9
more tables, using match with temp AA. How to avoid repetion of creating 9
more times temp table AA.
I would like to do insert in all 10 table in one step.
Thank's in advance Leny G.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1

Jun 27 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
lenygold via DBMonster.com wrote:
Hi everybody:
What is the best way to
I have 10 tables with similar INSERT requiremnts.
INSERT INTO ACSB.VAATAFAE
WITH AA(AA_TIN, AA_FILE_SOURCE_CD, .AA_TIN_TYP) AS
( SELECT AA_TIN, AA_FILE_SOURCE_CD, .AA_TIN_TYP
FROM VAATAFAA
WHERE AB_TP_ACNT_STAT_CD <0),

AE(ACS_TIN, ACS_FILE_SOURCE_CD, ACS_TIN_TYP………….) AS

(SELECT * FROM ACSB.VAATAFAE
INNER JOIN AA
ON AA .AA_FILE_SOURCE_CD = ACS_FILE_SOURCE_CD
AND
AA AA_TIN_TYP = ACS_TIN_TYP
AND
AA. AA_FILE_SOURCE_CD = ACS_FILE_SOURCE_CD)
SELECT * FOM AE;
Table VATATFAA HAS 10 MILS ROWS. AFTER SELECT IN TEMP AA ONLY 50k ROWS LEFT.
I insert IN ACSB.VAATAFAE ONLY MAYCHES WITH AA, but i have to insert in 9
more tables, using match with temp AA. How to avoid repetion of creating 9
more times temp table AA.
I would like to do insert in all 10 table in one step.
The answer differs slightly whether each row gets inserted (partially)
into each target table or you have a (partial) partitioning of rows.

-- Split the source vertically (each column to another table)
CREATE TABLE T1(c1 INT);
CREATE TABLE T2(c2 INT);
CREATE TABLE T3(c3 INT);
WITH source(c1, c2, c3) AS (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)),
ins1(c1, c2, c3) AS (SELECT c1, c2, c3
FROM NEW TABLE(INSERT INTO T1(c1)
INCLUDE (c2 INT, c3 INT)
SELECT * FROM source)),
ins2(c1, c2, c3) AS (SELECT c1, c2, c3
FROM NEW TABLE(INSERT INTO T2(c2)
INCLUDE (c1 INT, c3 INT)
SELECT c2, c1, c3 FROM ins1)),
ins3(c1, c2, c3) AS (SELECT c1, c2, c3
FROM NEW TABLE(INSERT INTO T3(c3)
INCLUDE (c1 INT, c2 INT)
SELECT c3, c1, c2 FROM ins2))
SELECT COUNT(1) AS rows_inserted FROM ins3;

ROWS_INSERTED
-------------
3

1 record(s) selected.

Access Plan:
-----------
Total Cost: 24.5865
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
24.5865
3
|
1
SORT
( 3)
24.515
3
|
1
GRPBY
( 4)
24.3584
3
|
3
INSERT
( 5)
24.3184
3
/---+---\
3 3
INSERT TABLE: SRIELAU
( 6) T3
16.2384
2
/---+--\
3 3
INSERT TABLE: SRIELAU
( 7) T2
8.1584
1
/---+--\
3 3
TBSCAN TABLE: SRIELAU
( 8) T1
0.0072
0
|
3
TABFNC: SYSIBM
GENROW
If you need to selectively insert rows each insert need to share the source:
WITH source(c1, c2, c3) AS (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)),
ins1(d) AS (SELECT 1
FROM NEW TABLE(INSERT INTO T1(c1)
SELECT c1 FROM source
WHERE c3 < 5)),
ins2(d) AS (SELECT 1
FROM NEW TABLE(INSERT INTO T2(c2)
SELECT c2 FROM source
WHERE c3 BETWEEN 5 AND 8)),
ins3(d) AS (SELECT 1
FROM NEW TABLE(INSERT INTO T3(c3)
SELECT c3 FROM source
WHERE c3 8))
VALUES 1;

Access Plan:
-----------
Total Cost: 31.338
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
31.338
3
|
1
SORT
( 3)
31.2665
3
|
1
NLJOIN
( 4)
31.1454
3

/-----------------+----------------\
1
1
UNION
TBSCAN
( 5)
( 14)
31.1406
0.0048
3
0
+-------------------------+-------------------------+
|
1 0.3 1
1
INSERT INSERT INSERT
TABFNC: SYSIBM
( 6) ( 10) ( 12)
GENROW
10.3603 10.4201 10.3603
1 1 1
/---+---\ /---+---\ /---+---\
1 1 0.3 1 1
1
TBSCAN TABLE: SRIELAU TBSCAN TABLE: SRIELAU TBSCAN
TABLE: SRIELAU
( 7) T1 ( 11) T2 ( 13) T3
2.28028 2.34008 2.28028
0 0 0
| | |
3 3 3
TEMP TEMP TEMP
( 8) ( 8) ( 8)
0.6802 0.6802 0.6802
0 0 0
|
3
TBSCAN
( 9)
0.0072
0
|
3
TABFNC: SYSIBM
GENROW

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 27 '08 #2

P: n/a
Thank you very mach Serge. This is greate.
Serge Rielau wrote:
>Hi everybody:
What is the best way to
[quoted text clipped - 22 lines]
>more times temp table AA.
I would like to do insert in all 10 table in one step.
The answer differs slightly whether each row gets inserted (partially)
into each target table or you have a (partial) partitioning of rows.

-- Split the source vertically (each column to another table)
CREATE TABLE T1(c1 INT);
CREATE TABLE T2(c2 INT);
CREATE TABLE T3(c3 INT);
WITH source(c1, c2, c3) AS (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)),
ins1(c1, c2, c3) AS (SELECT c1, c2, c3
FROM NEW TABLE(INSERT INTO T1(c1)
INCLUDE (c2 INT, c3 INT)
SELECT * FROM source)),
ins2(c1, c2, c3) AS (SELECT c1, c2, c3
FROM NEW TABLE(INSERT INTO T2(c2)
INCLUDE (c1 INT, c3 INT)
SELECT c2, c1, c3 FROM ins1)),
ins3(c1, c2, c3) AS (SELECT c1, c2, c3
FROM NEW TABLE(INSERT INTO T3(c3)
INCLUDE (c1 INT, c2 INT)
SELECT c3, c1, c2 FROM ins2))
SELECT COUNT(1) AS rows_inserted FROM ins3;

ROWS_INSERTED
-------------
3

1 record(s) selected.

Access Plan:
-----------
Total Cost: 24.5865
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
24.5865
3
|
1
SORT
( 3)
24.515
3
|
1
GRPBY
( 4)
24.3584
3
|
3
INSERT
( 5)
24.3184
3
/---+---\
3 3
INSERT TABLE: SRIELAU
( 6) T3
16.2384
2
/---+--\
3 3
INSERT TABLE: SRIELAU
( 7) T2
8.1584
1
/---+--\
3 3
TBSCAN TABLE: SRIELAU
( 8) T1
0.0072
0
|
3
TABFNC: SYSIBM
GENROW

If you need to selectively insert rows each insert need to share the source:
WITH source(c1, c2, c3) AS (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)),
ins1(d) AS (SELECT 1
FROM NEW TABLE(INSERT INTO T1(c1)
SELECT c1 FROM source
WHERE c3 < 5)),
ins2(d) AS (SELECT 1
FROM NEW TABLE(INSERT INTO T2(c2)
SELECT c2 FROM source
WHERE c3 BETWEEN 5 AND 8)),
ins3(d) AS (SELECT 1
FROM NEW TABLE(INSERT INTO T3(c3)
SELECT c3 FROM source
WHERE c3 8))
VALUES 1;

Access Plan:
-----------
Total Cost: 31.338
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
31.338
3
|
1
SORT
( 3)
31.2665
3
|
1
NLJOIN
( 4)
31.1454
3

/-----------------+----------------\
1
1
UNION
TBSCAN
( 5)
( 14)
31.1406
0.0048
3
0
+-------------------------+-------------------------+
|
1 0.3 1
1
INSERT INSERT INSERT
TABFNC: SYSIBM
( 6) ( 10) ( 12)
GENROW
10.3603 10.4201 10.3603
1 1 1
/---+---\ /---+---\ /---+---\
1 1 0.3 1 1
1
TBSCAN TABLE: SRIELAU TBSCAN TABLE: SRIELAU TBSCAN
TABLE: SRIELAU
( 7) T1 ( 11) T2 ( 13) T3
2.28028 2.34008 2.28028
0 0 0
| | |
3 3 3
TEMP TEMP TEMP
( 8) ( 8) ( 8)
0.6802 0.6802 0.6802
0 0 0
|
3
TBSCAN
( 9)
0.0072
0
|
3
TABFNC: SYSIBM
GENROW
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1

Jun 27 '08 #3

P: n/a
Thank's again SERGE.
I just tested with my tables in DB2 9.5 EXPRESS C and it is working perfect:

WITH
NEED_INSERT(SSN,NAME,DOB,AGE,PL_BIRTH,MARITAL_STAT ) AS
(SELECT * FROM FAMILY
WHERE SSN BETWEEN 777777777 AND 999999999),
INS1(X) AS (SELECT 1 FROM NEW TABLE(INSERT INTO NEW_FAMILY (SSN,NAME,DOB,
AGE,PL_BIRTH,MARITAL_STAT)
SELECT * FROM NEED_INSERT)),
INS2(X) AS (SELECT 1 FROM NEW TABLE(INSERT INTO OLD_FAMILY(SSN,NAME,DOB,AGE,
PL_BIRTH,MARITAL_STAT)
SELECT * FROM NEED_INSERT))
SELECT COUNT(X) FROM INS2;

The question is will it work on mainframe with DB2 OZ VERSION 8.2?

lenygold wrote:
>Hi everybody:
What is the best way to
I have 10 tables with similar INSERT requiremnts.

INSERT INTO ACSB.VAATAFAE
WITH AA(AA_TIN, AA_FILE_SOURCE_CD, .AA_TIN_TYP) AS
( SELECT AA_TIN, AA_FILE_SOURCE_CD, .AA_TIN_TYP
FROM VAATAFAA
WHERE AB_TP_ACNT_STAT_CD <0),

AE(ACS_TIN, ACS_FILE_SOURCE_CD, ACS_TIN_TYP………….) AS

(SELECT * FROM ACSB.VAATAFAE
INNER JOIN AA
ON AA .AA_FILE_SOURCE_CD = ACS_FILE_SOURCE_CD
AND
AA AA_TIN_TYP**** = ACS_TIN_TYP
AND
AA. AA_FILE_SOURCE_CD = ACS_FILE_SOURCE_CD)
SELECT * FOM AE;

Table VATATFAA HAS 10 MILS ROWS. AFTER SELECT IN TEMP AA ONLY 50k ROWS LEFT.
I insert IN ACSB.VAATAFAE ONLY MAYCHES WITH AA, but i have to insert in 9
more tables, using match with temp AA. How to avoid repetion of creating 9
more times temp table AA.
I would like to do insert in all 10 table in one step.
Thank's in advance Leny G.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1

Jun 27 '08 #4

P: n/a
lenygold via DBMonster.com wrote:
Thank's again SERGE.
I just tested with my tables in DB2 9.5 EXPRESS C and it is working perfect:

WITH
NEED_INSERT(SSN,NAME,DOB,AGE,PL_BIRTH,MARITAL_STAT ) AS
(SELECT * FROM FAMILY
WHERE SSN BETWEEN 777777777 AND 999999999),
INS1(X) AS (SELECT 1 FROM NEW TABLE(INSERT INTO NEW_FAMILY (SSN,NAME,DOB,
AGE,PL_BIRTH,MARITAL_STAT)
SELECT * FROM NEED_INSERT)),
INS2(X) AS (SELECT 1 FROM NEW TABLE(INSERT INTO OLD_FAMILY(SSN,NAME,DOB,AGE,
PL_BIRTH,MARITAL_STAT)
SELECT * FROM NEED_INSERT))
SELECT COUNT(X) FROM INS2;

The question is will it work on mainframe with DB2 OZ VERSION 8.2?
I'm not aware of the existence of DB2 V8.2 for zOS.
You will need to replace NEW TABLE with FINAL TABLE for compatibility
with DB2 zOS. I am doubtful however that V8 of DB2 zOS supports insert
in the WITH clause. Best check the cross platform SQL Ref.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 27 '08 #5

P: n/a
lenygold via DBMonster.com wrote:
Thank's again SERGE.
I just tested with my tables in DB2 9.5 EXPRESS C and it is working
perfect:

WITH
NEED_INSERT(SSN,NAME,DOB,AGE,PL_BIRTH,MARITAL_STAT ) AS
(SELECT * FROM FAMILY
WHERE SSN BETWEEN 777777777 AND 999999999),
INS1(X) AS (SELECT 1 FROM NEW TABLE(INSERT INTO NEW_FAMILY
(SSN,NAME,DOB, AGE,PL_BIRTH,MARITAL_STAT)
SELECT * FROM NEED_INSERT)),
INS2(X) AS (SELECT 1 FROM NEW TABLE(INSERT INTO
OLD_FAMILY(SSN,NAME,DOB,AGE, PL_BIRTH,MARITAL_STAT)
SELECT * FROM NEED_INSERT))
SELECT COUNT(X) FROM INS2;

The question is will it work on mainframe with DB2 OZ VERSION 8.2?
DB2 for z/OS 8.2 might support it. There's *very* limited support for
data change table references in 8.2 (only FINAL TABLE(INSERT) is
supported, and there's lots of gotchas). If you change NEW TABLE to
FINAL TABLE it /might/ work but I'm not sure; one of the aforementioned
gotchas might restrict this syntax to top-level SELECTs only, but I
can't remember off the top of my head.

DB2 for z/OS 9.1 should support it - it has similar data change table
references to DB2 for LUW although like DB2 for z/OS 8.2, it only
supports FINAL TABLE (not NEW TABLE) - and it additionally supports
FINAL TABLE(MERGE) which is something even DB2 for LUW lacks. So,
again, you'd need to replace NEW TABLE with FINAL TABLE.
Cheers,

Dave.
Jun 27 '08 #6

P: n/a
Dave Hughes wrote:
lenygold via DBMonster.com wrote:
[snip]
The question is will it work on mainframe with DB2 OZ VERSION 8.2?

DB2 for z/OS 8.2 might support it. There's very limited support for
data change table references in 8.2 (only FINAL TABLE(INSERT) is
supported, and there's lots of gotchas). If you change NEW TABLE to
FINAL TABLE it might work but I'm not sure; one of the aforementioned
gotchas might restrict this syntax to top-level SELECTs only, but I
can't remember off the top of my head.

DB2 for z/OS 9.1 should support it - it has similar data change table
references to DB2 for LUW although like DB2 for z/OS 8.2, it only
supports FINAL TABLE (not NEW TABLE) - and it additionally supports
FINAL TABLE(MERGE) which is something even DB2 for LUW lacks. So,
again, you'd need to replace NEW TABLE with FINAL TABLE.
Doh! Brain not functioning... As Serge's pointed out - make that DB2
for z/OS 8 (not .2 :-)

Cheers,

Dave.
Jun 27 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.