Multitable insert in DB2 v8 os no Merge is avilable:

[lenygold via DBMonster.com]

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.aspx/ibm-db2/200805/1

[Serge Rielau]

Re: Multitable insert in DB2 v8 os no Merge is avilable:

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

[lenygold via DBMonster.com]

Re: Multitable insert in DB2 v8 os no Merge is avilable:

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_INSERTE D
>-------------
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.aspx/ibm-db2/200805/1

[lenygold via DBMonster.com]

Re: Multitable insert in DB2 v8 os no Merge is avilable:

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,P L_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,MA RITAL_STAT)
SELECT * FROM NEED_INSERT)),
INS2(X) AS (SELECT 1 FROM NEW TABLE(INSERT INTO OLD_FAMILY(SSN, NAME,DOB,AGE,
PL_BIRTH,MARITA L_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.aspx/ibm-db2/200805/1

[Serge Rielau]

Re: Multitable insert in DB2 v8 os no Merge is avilable:

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,P L_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,MA RITAL_STAT)
SELECT * FROM NEED_INSERT)),
INS2(X) AS (SELECT 1 FROM NEW TABLE(INSERT INTO OLD_FAMILY(SSN, NAME,DOB,AGE,
PL_BIRTH,MARITA L_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

[Dave Hughes]

Re: Multitable insert in DB2 v8 os no Merge is avilable:

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,P L_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,MA RITAL_STAT)
SELECT * FROM NEED_INSERT)),
INS2(X) AS (SELECT 1 FROM NEW TABLE(INSERT INTO
OLD_FAMILY(SSN, NAME,DOB,AGE, PL_BIRTH,MARITA L_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.

[Dave Hughes]

Re: Multitable insert in DB2 v8 os no Merge is avilable:

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.