Multitable insert in DB2 v8 os no Merge is avilable:

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lenygold - 23 May 2008 13:20 GMT

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.

Reply to this Message

Serge Rielau - 26 May 2008 14:03 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

lenygold - 26 May 2008 22:34 GMT

Thank you very mach Serge. This is greate.

>> Hi everybody:
>> What is the best way to
[quoted text clipped - 176 lines]
> TABFNC: SYSIBM
> GENROW

Reply to this Message

lenygold - 26 May 2008 22:59 GMT

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?

>Hi everybody:
>What is the best way to
[quoted text clipped - 23 lines]
>I would like to do insert in all 10 table in one step.
>Thank's in advance Leny G.

Reply to this Message

Serge Rielau - 27 May 2008 12:49 GMT

> Thank's again SERGE.
> I just tested with my tables in DB2 9.5 EXPRESS C and it is working perfect:
[quoted text clipped - 12 lines]
>
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Dave Hughes - 27 May 2008 12:54 GMT

> Thank's again SERGE.
> I just tested with my tables in DB2 9.5 EXPRESS C and it is working
[quoted text clipped - 13 lines]
>
> 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.

Reply to this Message

Dave Hughes - 27 May 2008 13:37 GMT

[snip]

> > The question is will it work on mainframe with DB2 OZ VERSION 8.2?
>
[quoted text clipped - 10 lines]
> 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.

Reply to this Message