General Questions- Need Clearance

[pankaj_wolfhunter]

Hi,
I need some clearance on the following questions:

1) Does LOAD command updates indexes defined for a table?

2) Is REPLACE option in the LOAD command a logged operation?

Help will be greatly appreciated.

TIA

[Serge Rielau]

pa***************@yahoo.co.in wrote:

Hi,
I need some clearance on the following questions:

1) Does LOAD command updates indexes defined for a table? Yes
2) Is REPLACE option in the LOAD command a logged operation? Interesting question. I'm quite sur eit is not.
Here is some hint I find in the docs of LOAD:
TERMINATE
One of four modes under which the load utility can execute.
Terminates a previously interrupted load operation, and rolls back the
operation to the point in time at which it started, even if consistency
points were passed. The states of any table spaces involved in the
operation return to normal, and all table objects are made consistent
(index objects might be marked as invalid, in which case index rebuild
will automatically take place at next access). If the load operation
being terminated is a load REPLACE, the table will be truncated to an
empty table after the load TERMINATE operation. If the load operation
being terminated is a load INSERT, the table will retain all of its
original records after the load TERMINATE operation.

The load terminate option will not remove a backup pending state
from table spaces.
Help will be greatly appreciated.

TIA

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[pankaj_wolfhunter]

Thanks for the reply serge.
I am still no clear.
"If the load operation
being terminated is a load REPLACE, the table will be truncated to an
empty table after the load TERMINATE operation"
Does this mean it is a non-logged operation as it TRUNCATES the table?
And i am still not clear with the indexes part?
TIA

[Serge Rielau]

pa***************@yahoo.co.in wrote:

Thanks for the reply serge.
I am still no clear.
"If the load operation
being terminated is a load REPLACE, the table will be truncated to an
empty table after the load TERMINATE operation"
Does this mean it is a non-logged operation as it TRUNCATES the table? I'm 99% sure of that. Shoudl be easy to test though. And i am still not clear with the indexes part?

LOAD maintains indexes

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[pankaj_wolfhunter]

Thanks for the reply Serge. I dont know what we do without ur
expertise. :-)

[Knut Stolze]

pa***************@yahoo.co.in wrote:

Hi,
I need some clearance on the following questions:

1) Does LOAD command updates indexes defined for a table?
LOAD has to maintain indexes, of course. How else would you ensure the
consistency of indexes and the data in the table?
2) Is REPLACE option in the LOAD command a logged operation?

DB2 needs to write a log record for the truncation, and it truly does so:
http://publib.boulder.ibm.com/infoce...n/r0001910.htm

I just loaded an empty file into a table using the REPLACE option. This
caused the table to be truncated, and it generated the following log
records. The 8th log record is probably the one you're interested in.
Database name : test
Start of LSN Range : 0.187.35944
End of LSN Range : 0.312.32780

================================================== =========================
Log record LSN : 0.187.35944
Next log record LSN : 0.187.36420

Previous log record in transaction : (null)
Transaction identifier : 0.0.287
Length of log record : 476 bytes

Type log record : [0x004A] Table load start
General flags : Propagatable
Written by : Utility Manager

Tablespace identifier : [2] USERSPACE1
Table identifier : [2] DB2I00.X

Table flags : [0x00000000]
Load identifier : LD01

---------------------------------------------------------------------------
Log record LSN : 0.187.36420
Next log record LSN : 0.187.36456

Previous log record in transaction : 0.187.35944
Transaction identifier : 0.0.287
Length of log record : 36 bytes

Type log record : [0x004E] Normal
General flags : Redo always
Written by : Data Management System (DMS)

Function identifier : [124] Alter table attributes
Tablespace identifier : [2] USERSPACE1
Table identifier : [2] DB2I00.X

Table attribute change : [0x00000008] Set table in LOAD state

---------------------------------------------------------------------------
Log record LSN : 0.187.36456
Next log record LSN : 0.187.36664

Previous log record in transaction : 0.187.36420
Transaction identifier : 0.0.287
Length of log record : 208 bytes

Type log record : [0x004E] Normal
General flags : None
Written by : Data Management System (DMS)

Function identifier : [120] Update record
Tablespace identifier : [2] USERSPACE1
Table identifier : [2] DB2I00.X

Page number : 0
Slot number of record/entry on page : 2
Old length of record on page : 76 bytes
Length of record (group) on page : 76 bytes
Free space on page : 2876 bytes
Type of record header : [0x00] (normal)
Storage type of record data : Internal control
Offset of record on page : 2916

---------------------------------------------------------------------------
Log record LSN : 0.187.36664
Next log record LSN : 0.187.36688

Previous log record in transaction : 0.187.36456
Transaction identifier : 0.0.287
Length of log record : 24 bytes

Type log record : [0x006B] Table load locator create
General flags : None
Written by : Utility Manager

Tablespace identifier : [2] USERSPACE1
Table identifier : [2] DB2I00.X
---------------------------------------------------------------------------
Log record LSN : 0.187.36688
Next log record LSN : 0.187.36726

Previous log record in transaction : 0.187.36664
Transaction identifier : 0.0.287
Length of log record : 38 bytes

Type log record : [0x0084] Normal commit
General flags : Propagatable
Written by : Transaction Manager

Authorization : DB2I00
Commit timestamp : Mon Oct 24 13:52:55 2005

---------------------------------------------------------------------------
Log record LSN : 0.187.36726
Next log record LSN : 0.187.37218

Previous log record in transaction : (null)
Transaction identifier : 0.0.288
Length of log record : 476 bytes

Type log record : [0x004A] Table load start
General flags : None
Written by : Utility Manager

Tablespace identifier : [2] USERSPACE1
Table identifier : [2] DB2I00.X

Table flags : [0x00000000]
Load identifier : LD02

---------------------------------------------------------------------------
Log record LSN : 0.187.37218
Next log record LSN : 0.187.37262

Previous log record in transaction : 0.187.36726
Transaction identifier : 0.0.288
Length of log record : 44 bytes

Type log record : [0x0052] Redo
General flags : Redo always
Written by : Data Object Manager (DOM)

Function identifier : [10] Change life Lsn
Tablespace identifier : [2] USERSPACE1
Table identifier : [2] DB2I00.X
Object tablespace : [2] USERSPACE1
Object identifier : [2] DB2I00.X (Table)
Object flags : 0x00000000

Old life LSN : 0.187.33188
Life LSN : 0.187.37218

---------------------------------------------------------------------------
Log record LSN : 0.187.37262
Next log record LSN : 0.187.37402

Previous log record in transaction : 0.187.36726
Transaction identifier : 0.0.288
Length of log record : 140 bytes

Type log record : [0x004E] Normal
General flags : None
Written by : Data Object Manager (DOM)

Truncate timestamp : Mon Oct 24 13:52:56 2005

Function identifier : [11] Truncate table
Tablespace identifier : [2] USERSPACE1
Table identifier : [2] DB2I00.X
Object tablespace : [2] USERSPACE1
Object identifier : [2] DB2I00.X (Table)
Object flags : 0x00000000

Long/LOB data tablespace : [2] USERSPACE1
Long data object identifier : [0]
LOB data object identifier : [0]
Index data tablespace : [2] USERSPACE1
Index data object identifier : [0]
Life LSN : 0.187.36726

---------------------------------------------------------------------------
Log record LSN : 0.187.37402
Next log record LSN : 0.187.37446

Previous log record in transaction : 0.187.37262
Transaction identifier : 0.0.288
Length of log record : 44 bytes

Type log record : [0x0052] Redo
General flags : Redo always
Written by : Data Object Manager (DOM)

Function identifier : [10] Change life Lsn
Tablespace identifier : [2] USERSPACE1
Table identifier : [2] DB2I00.X
Object tablespace : [2] USERSPACE1
Object identifier : [2] DB2I00.X (Table)
Object flags : 0x00000000

Old life LSN : 0.187.37218
Life LSN : 0.187.37402

---------------------------------------------------------------------------
Log record LSN : 0.187.37446
Next log record LSN : 0.250.12

Previous log record in transaction : 0.187.37262
Transaction identifier : 0.0.288
Length of log record : 7336 bytes

Type log record : [0x008A] Local pending list
General flags : None
Written by : Transaction Manager

Commit timestamp : Mon Oct 24 13:52:56 2005

---------------------------------------------------------------------------
Log record LSN : 0.250.12
Next log record LSN : 0.250.94

Previous log record in transaction : (null)
Transaction identifier : 0.0.429
Length of log record : 82 bytes

Type log record : [0x004E] Normal
General flags : None
Written by : Data Management System (DMS)

Function identifier : [118] Insert record
Tablespace identifier : [0] SYSCATSPACE
Table identifier : [13] SYSIBM.SYSDBAUTH

Page number : 0
Slot number of record/entry on page : 6
Length of record (group) on page : 44 bytes
Free space on page : 2650 bytes
Type of record header : [0x00] (normal)
Storage type of record data : Data without VALUE COMPRESSION
Offset of record on page : 2664
Length of fixed data of record : 20 bytes

---------------------------------------------------------------------------
Log record LSN : 0.250.94
Next log record LSN : 0.250.173

Previous log record in transaction : 0.250.12
Transaction identifier : 0.0.429
Length of log record : 79 bytes

Type log record : [0x004E] Normal
General flags : None
Written by : Index Manager

Function identifier : [101] Add key to index
Tablespace identifier : [0] SYSCATSPACE
Table identifier : [13] SYSIBM.SYSDBAUTH
Object tablespace : [0] SYSCATSPACE
Object identifier : [13] (Index)

Root page of index : 2
Page number : 2
Slot number of record/entry on page : 2
Offset of key data in index entry : 0

---------------------------------------------------------------------------
Log record LSN : 0.250.173
Next log record LSN : 0.312.32780

Previous log record in transaction : 0.250.94
Transaction identifier : 0.0.429
Length of log record : 28 bytes

Type log record : [0x0084] Normal commit
General flags : None
Written by : Transaction Manager

Commit timestamp : Mon Oct 24 14:08:04 2005

================================================== =========================
read log records : 13 records
read log bytes : 9011 bytes
worktime : 0.02 seconds

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[pankaj_wolfhunter]

Thanks for the replies. U guys are gr8. :-)

[juliane26]

Knut, I think the practical question is not so only whether I do have a
log entry (which might be needed for rollforward, HADR etc).

Still, this log entry is not enough to say 'rollback': not every row
getting deleted gets a log entry. It is not logged in a sense that I
can rollback the action or recover the data incase of problems without
having a backup.

Pankaj: as for all the phases, when indexes are rebuilt etc, check out:
http://publib.boulder.ibm.com/infoce...n/c0004587.htm

[Pierre Saint-Jacques]

1) LOAD will update/maintain indexes. Look at the INDEXING MODE parameter
in the LOAD command documentation.

2)
a) LOAD is a logged command by itself.
b) The data rows that are loaded are NOT logged.
c) If you have unique indexes defined and are using exception tables to
"trap" duplicates. then:
1) The load command will first delete the duplicate actual rows
inserted in the table in phase one of the load and then 2) will
insert the same rowa in the exception table.

Those deletes and inserts will be logged.

HTH, Pierre.
-
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<pa***************@yahoo.co.in> a écrit dans le message de news:
11*********************@g44g2000cwa.googlegroups.c om...

Hi,
I need some clearance on the following questions:

1) Does LOAD command updates indexes defined for a table?

2) Is REPLACE option in the LOAD command a logged operation?

Help will be greatly appreciated.

TIA

[Knut Stolze]

juliane26 wrote:

Still, this log entry is not enough to say 'rollback': not every row
getting deleted gets a log entry. It is not logged in a sense that I
can rollback the action or recover the data incase of problems without
having a backup.

True. But keep in mind the purpose of LOAD: to get data into the database
really _fast_. That implies to avoid the overhead associated with logging
as much as possible. So you cannot undo a LOAD REPLACE operation with a
rollback.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany