06-04-2018
I expect that this is doing what you asked, and although it does seem a sensible query, it is not being very practical. I think that your request is going to build some huge temporary tables to work out the various clauses before applying any inserts/updates. You may even fill your temporary table tablespace before your MERGE completes this step.
You might have some success with indices though. What indices do you have on table
schemaname.Customer_Staging and
schemaname.Customer_Staging?
- If you don't have a single index for the three columns you mention in the SELECT DISTINCT, then you might do a full table scan of schemaname.Customer_Staging.
- If you don't have a single index for all the columns in the whole query (e.g A.Name, B.Name, A.Load_dt etc. ) then you might do a full table scan of the appropriate table.
Any reason to do a full table scan that these scales will be bad. An index might take a while to build and needs to have space, but then your MERGE should run better. I've had something that took me 20 minutes to build an index then the process I wanted ran in about an hour, after which I dropped the index again.because it was a one-off report. The run without the index was abandoned after over 22 hours (someone set if off and went home)
Is there some sort of query profiler you can use to consider this? MSSQLServer has one, Oracle has one so I'm sure that DB2 will have one, but I've not ever used it. You need to avoid a full table scan when dealing with this volume of data.
I hope that this helps,
Robin
Last edited by rbatte1; 06-04-2018 at 08:03 AM..
Reason: Spelling correction.
9 More Discussions You Might Find Interesting
1. UNIX for Advanced & Expert Users
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)
Discussion started by: shibz
5 Replies
2. Shell Programming and Scripting
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)
Discussion started by: baskivs
2 Replies
3. Shell Programming and Scripting
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)
Discussion started by: msp2244
1 Replies
4. Shell Programming and Scripting
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)
Discussion started by: neelmani
6 Replies
5. Shell Programming and Scripting
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)
Discussion started by: suresh_target
1 Replies
6. Shell Programming and Scripting
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)
Discussion started by: bhaskar v
1 Replies
7. Shell Programming and Scripting
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)
Discussion started by: vamsi.valiveti
13 Replies
8. Programming
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)
Discussion started by: Perlbaby
2 Replies
9. Shell Programming and Scripting
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 PHP
db2_column_privileges
DB2_COLUMN_PRIVILEGES(3) 1 DB2_COLUMN_PRIVILEGES(3)
db2_column_privileges - Returns a result set listing the columns and associated privileges for a table
SYNOPSIS
resource db2_column_privileges (resource $connection, [string $qualifier], [string $schema], [string $table-name], [string $column-
name])
DESCRIPTION
Returns a result set listing the columns and associated privileges for a table.
PARAMETERS
o $connection
- A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.
o $qualifier
- A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.
o $schema
- The schema which contains the tables. To match all schemas, pass NULL or an empty string.
o $table-name
- The name of the table or view. To match all tables in the database, pass NULL or an empty string.
o $column-name
- The name of the column. To match all columns in the table, pass NULL or an empty string.
RETURN VALUES
Returns a statement resource with a result set containing rows describing the column privileges for columns matching the specified parame-
ters. The rows are composed of the following columns:
+-------------+---------------------------------------------------+
|Column name | |
| | |
| | Description |
| | |
+-------------+---------------------------------------------------+
| TABLE_CAT | |
| | |
| | Name of the catalog. The value is NULL if this |
| | table does not have catalogs. |
| | |
|TABLE_SCHEM | |
| | |
| | Name of the schema. |
| | |
| TABLE_NAME | |
| | |
| | Name of the table or view. |
| | |
|COLUMN_NAME | |
| | |
| | Name of the column. |
| | |
| GRANTOR | |
| | |
| | Authorization ID of the user who granted the |
| | privilege. |
| | |
| GRANTEE | |
| | |
| | Authorization ID of the user to whom the privi- |
| | lege was granted. |
| | |
| PRIVILEGE | |
| | |
| | The privilege for the column. |
| | |
|IS_GRANTABLE | |
| | |
| | Whether the GRANTEE is permitted to grant this |
| | privilege to other users. |
| | |
+-------------+---------------------------------------------------+
SEE ALSO
db2_columns(3), db2_foreign_keys(3), db2_primary_keys(3), db2_procedure_columns(3), db2_procedures(3), db2_special_columns(3), db2_statis-
tics(3), db2_table_privileges(3), db2_tables(3).
PHP Documentation Group DB2_COLUMN_PRIVILEGES(3)