Dear Team,
I am using DB2 v10.5 and trying to load huge data using MERGE option ( 10-12 Million) using below query. Basically it loads data from staging to target . Staging table schemaname.Customer_Staging has 12 Million records . Runs for 25 Mins for just select query. But while doing merge (first time load) , it has to insert all records and below process is getting hanged after running 1.5-2 hrs (With No inserts)
HTML Code:
MERGE INTO schemaname.Customer_Table A
USING ( SELECT DISTINCT B.Name , B.CUST_ID, B.ORG_ID from schemaname.Customer_Staging B
WHERE (B.Name is not null and length(B.Name)>0) and (B.CUST_ID is not null and length(B.CUST_ID)>0) and (B.ORG_ID is NOT null and length(B.ORG_ID) > 0)) B
ON B.Name = A.Name and B.CUST_ID=A.CUST_ID and B.ORG_ID=A.ORG_ID and A.Load_dt is null
WHEN NOT MATCHED THEN INSERT (Name,CUST_ID,Load_dt,ORG_ID)
VALUES(B.Name,B.CUST_ID,current timestamp - current timeZone,B.ORG_ID);
DDL columns for both staging and Target is same .
Included Unique index for three columns Name,CUST_ID,ORG_ID using
Code:
ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC option
Please can someone help how to tweak this query or use best approach.
any help appreciated
Thanks
Hello all,
I just stuck up in an uncertain situation related to network performance...
I am trying to access one of my remote client unix machine from a distant location..
The client machine is Ultra-5_10 , with SunOS 5.5.1
The ndd result ( hme1 )shows that the machine is hooked to a... (5 Replies)
Hi
i m trying to connect DB2 via unix. it is successfully connect. but the connect is getting disconnect .
below is the query ,
countvalue=$(db2 "connect to <Database> user <username> using <Password>" | db2 -x 'select count(*) from <tablename>' ); echo $countvalue
while... (2 Replies)
Hi
I am extracting a column value(DESCRIPTION) from one table and passing it to another db2 statement in a shell code to fetch some value(ID) but the value when passed in where condition is taking as newline+value.
Please find the out put when executed:
+ echo description is ::::... (1 Reply)
hi friends,
i have a file where every word is present in a new line for example:
more file1:
i want to fetch previous line wherever i am getting "as" as a keyword.
i tried at home the follwing code in linex:
grep -B 1 "as" file1
ouput:
caste
caste1
it was working!!
but now i am... (6 Replies)
Hi all,
I am new for linux environment, and i am working as a DBA.
I am facing some issues in OS level:
In our dev boxes /db2home under this directory i'm not finding any folder but it's showing 98% used .
/dev/dm-14 5.0G 4.6G 115M 98% /db2home
# ls -lrt
total 16... (1 Reply)
hi
i am trying to execute db2 queries through shell script. it's working fine but for few queries is not working ( those queries are taking time so the script is not waiting to get the complete the execution of that query )
could you please any one help me on this
is there any wait... (1 Reply)
I have a shell script main.ksh We are calling dbscript.ksh from main.ksh
I am using select statement in dbscript.ksh but there is a problem with the select statement in dbscript.ksh but still echo $? is showing as zero. I am using DB2 commands in dbscript.ksh
Main.ksh
dbscript.ksh
echo $? ... (13 Replies)
Dear Team
Have this interesting question on how to determine cost savings(USD) based on performance tuning in Db2
I am using DB2 v10.5 . I worked on db2 procedure that loaded 20Million records in just 2 Mins.
ETL execution time reduced from 30 Mins to 2 Mins.
From 15 Hrs Monthly to 1... (2 Replies)
Hi Gurus,
I need to merge two files.
file1 (small file, only one line)
this is first linefile2 (large file)
abc
def
ghi
... I use below command to merge the file, since the file2 is really large file, the command read whole file2, the performance is not good.
cat file1 > file3... (7 Replies)
Discussion started by: green_k
7 Replies
LEARN ABOUT REDHAT
insert
INSERT(7) SQL Commands INSERT(7)NAME
INSERT - create new rows in a table
SYNOPSIS
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | SELECT query }
INPUTS
table The name (optionally schema-qualified) of an existing table.
column The name of a column in table.
DEFAULT VALUES
All columns will be filled by null values or by values specified when the table was created using DEFAULT clauses.
expression
A valid expression or value to assign to column.
DEFAULT
This column will be filled in by the column DEFAULT clause, or NULL if a default is not available.
query A valid query. Refer to the SELECT statement for a further description of valid arguments.
OUTPUTS
INSERT oid 1
Message returned if only one row was inserted. oid is the numeric OID of the inserted row.
INSERT 0 #
Message returned if more than one rows were inserted. # is the number of rows inserted.
DESCRIPTION
INSERT allows one to insert new rows into a table. One can insert a single row at a time or several rows as a result of a query. The col-
umns in the target list may be listed in any order.
Each column not present in the target list will be inserted using a default value, either a declared DEFAULT value or NULL. PostgreSQL will
reject the new column if a NULL is inserted into a column declared NOT NULL.
If the expression for each column is not of the correct data type, automatic type coercion will be attempted.
You must have insert privilege to a table in order to append to it, as well as select privilege on any table specified in a WHERE clause.
USAGE
Insert a single row into table films:
INSERT INTO films VALUES
('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');
In this second example the last column len is omitted and therefore it will have the default value of NULL:
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');
In the third example, we use the DEFAULT values for the date columns rather than specifying an entry.
INSERT INTO films VALUES
('UA502','Bananas',105,DEFAULT,'Comedy',INTERVAL '82 minute');
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
Insert a single row into table distributors; note that only column name is specified, so the omitted column did will be assigned its
default value:
INSERT INTO distributors (name) VALUES ('British Lion');
Insert several rows into table films from table tmp:
INSERT INTO films SELECT * FROM tmp;
Insert into arrays (refer to the PostgreSQL User's Guide for further information about arrays):
-- Create an empty 3x3 gameboard for noughts-and-crosses
-- (all of these queries create the same board attribute)
INSERT INTO tictactoe (game, board[1:3][1:3])
VALUES (1,'{{"","",""},{},{"",""}}');
INSERT INTO tictactoe (game, board[3][3])
VALUES (2,'{}');
INSERT INTO tictactoe (game, board)
VALUES (3,'{{,,},{,,},{,,}}');
COMPATIBILITY
SQL92
INSERT is fully compatible with SQL92. Possible limitations in features of the query clause are documented for SELECT [select(7)].
SQL - Language Statements 2002-11-22 INSERT(7)