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 6  3699 
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
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
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
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
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Spare Brain |
last post by:
Hi Folks,
I need to INSERT data into the table where the row may already be present.
Can MERGE help me out? I'm limited to using SQL only, and thew DB is Oracle
9.2.
The low-tech solution...
|
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums...
|
by: Alex |
last post by:
Hi,
I need to create a trigger that will trap the insert commands on a
table and if the row already exists, it updates the information.
I started with this exemple but im getting syntax...
|
by: tomtailor |
last post by:
Hello!
I have a before insert Trigger and I want to catch if there is a
duplicate Key Error. If the Key already exists I'd like to update else
insert the row.
OK I am at the point I did the...
|
by: rAinDeEr |
last post by:
Hi,
I have a table with 2 columns
** CREATE TABLE test (emp_num DECIMAL(7) NOT NULL,emp_name CHAR(10) NOT
NULL) and i have inserted a number of records.
** Now, I want to insert a new...
|
by: bchi49 |
last post by:
I'm having problem updating a form that queries from 3 tables. When I
update the foreign key it tries to update the PK on the foreign key
table which it shouldn't. I'm using MS Access 2003...
|
by: rhaazy |
last post by:
Using MS SQL 2000
I have a stored procedure that processes an XML file generated from an
Audit program. The XML looks somewhat like this:
<ComputerScan>
<scanheader>...
|
by: John Cosmas |
last post by:
I have a DATATABLE which I have populated in my application, and I need it
written out to a particular table I specify in my ACCESS database. My code
works to the point of the MERGE and UPDATE,...
|
by: Henry J. |
last post by:
My app needs to insert thousand value rows into a mostly empty table
(data are read from a file). I can either use inserts, or use merge.
The advantage of using merge is that in the few cases...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
