The environment is the following one: I make import of an archive
text for a table (not declared global temporary table), a common
table. From this point it is that it starts has to occur low the
performance. In the TABLE01 (temporary table) import of the archive
is made text. Import is made daily. Each process of importation
loads 200,000 approximately rows.
CREATE TABLE TABLE01 (
COL01 INTEGER NOT NULL,
COLCOD02 CHAR(1) NOT NULL,
COL03 BIGINT NOT NULL,
COL04 CHAR(1) NOT NULL,
COL05 SMALLINT,
COL06 SMALLINT,
COL07 INTEGER,
COL08 SMALLINT,
COL09 DATE,
COL10 VARCHAR(70),
COL11 VARCHAR(70),
PRIMARY KEY (COL01)
);
Through the loaded information in the TABLE01 INSERTS UPDATES and
DELETES in other tables will be made. However for each row deals in
the TABLE01 is necessary to inquire if it exists in tabela02. That is
made through primary key. The existence or not existence in the
TABLE02 generate a process of INSERT, UPDATE or DELETE in other
tables and arrow one of the FLAGS related in the TABLE02.
CREATE TABLE TABLE02 (
COL01 INTEGER NOT NULL,
COLCOD02 BIGINT NOT NULL,
COL03 CHAR(1) NOT NULL,
COL04 VARCHAR(70) NOT NULL,
COL05 VARCHAR(70),
COL06 DATE,
COL07 CHAR(1),
COL08 VARCHAR(70),
COL09 CHAR(1) NOT NULL DEFAULT '0',
COL10 TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
COL11 TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
COLFLAG12 CHAR(1) NOT NULL DEFAULT '0',
COLFLAG13 CHAR(1) NOT NULL DEFAULT '0',
COLFLAG14 CHAR(1) NOT NULL DEFAULT '0',
PRIMARY KEY (COL01)
);
Who makes this process of distribution of data is a Stored Procedure
that contains the logic of as this distribution of data will occur.
The problem is that this this delaying around 24h, and I am not
obtaining to discover where this the pass.
The logic of stored procedure nothing more is of that to make FETCH in
the TABLE01 and through conditional commands, and some SELECTS in the
TABLE02, to discover which action to take when comparative with each
row of the TABLE02. Comment: TABLE02 contain 15.000.000 rows. I
thank the aid.
Best Regards
Schardosim
"Mark A" <ma@switchboard.net> wrote in message news:<5V******************@news.uswest.net>...
"Schardosim" <sc******@hotmail.com> wrote in message
news:26**************************@posting.google.c om... HI,
I am making the importation of 200.000 rows for one temporary table.
To leave of this temporary table insert, updates and deletes for other
tables are executed. However the reply time that I am time is very
high, 24h to execute 200.000. My environment is AIX 433, with DB2
7.2, Fixpak 8. If somebody will have some tip to improve this
performance, is grateful.
Best Regards
Schardosim
Not sure what you trying to do. Is this a declared global temporary table?
Are you inserting 200,000 rows or using the import command (which I am not
sure is possible on a declared global temporary table).
Please show all DDL for table and indexes, and all DML or commands that are
being executed.