why is it a SELECT statement writing logical log ?

[vpshriyan]

We all know that only manipulation (DML) statements like insert,update and delete are logged in a database created with log facility. I would like to know why a sub-query involved SQL SELECT statement generates logical log data!

Example:
Test these commands in single user mode so that other sessions are not involved in the logs. Even a checkpoint also adds certain entries in the logical log. Execute following commands immediately after a checkpoint is completed or force a checkpoint using onmode -c before proceeding.

1. Note down the current log position and %used using onstat -l
2. Using dbaccess run SQL: select rowid from systables where tabid in (select tabid from syscolumns) or to have greater effect one can use any user defined tables having huge rows for sub-queries.
3. Note the %used and calculate the difference.

I have noticed this behaviour using IDS 7.31.UC5 for HP-UX 10.2 and IDS 2000 Version 9.20.UC1 for RedHat Linux 7.2

Regards,
Shriyan

"LIKE A SPRING ALL THINGS BIG STARTS SMALL"

 

[ladwig]

Hi Shriyan,
I tested this. I used IDS 7.30.UC2 on Unixware 7.1.0.
But the select wasn't logged. Before testing I made ontape -a saved current log. The next log was filled with 0.00 %, after select again 0.00 %. I tried 3 times this select , result 0.00 %. Anything else wouldn't be nice.

 

[vpshriyan]

Hi,

select rowid from systables where tabid in (select tabid from syscolumns) generates entries similar to the following in the logical log.

Informix Dynamic Server Logical Log display
Software Serial Number ...........
Copyright (C) 1987-1998 Informix Software, Inc.

log number: 280.

addr len type xid id link
c33130 40 BEGIN 27 280 0 08/22/2002 15:55:51 205 informix
c33158 128 BLDCL 27 0 c33130 100030 8 8 4 0 _temptable
c331d8 36 CHALLOC 27 0 c33158 126de3 8
c331fc 40 PTEXTEND 27 0 c331d8 100030 7 126de3
c33224 36 COMMIT 27 0 c331fc 08/22/2002 15:55:51
c33248 40 BEGIN 27 280 0 08/22/2002 15:55:52 205 informix
c33270 32 ERASE 27 0 c33248 100030
c33290 36 CHFREE 27 0 c33270 126de3 8
c332b4 36 COMMIT 27 0 c33290 08/22/2002 15:55:52

The Log Browsing Utility onlog is been used here to asertain the effects. One can issue onlog with different variation.

onlog -n <nnnn> unique log id
-u <user> specific user related logs
-l long listing format


onlog -n 280 -u informix -l generated long listing format of the above:
-----------------------------------------------------------------------
Informix Dynamic Server Logical Log display
Software Serial Number ...........
Copyright (C) 1987-1998 Informix Software, Inc.

log number: 280.

addr len type xid id link
c33130 40 BEGIN 27 280 0 08/22/2002 15:55:51 205 informix
00000028 01180001 00000000 0000001b ...(.... ........
00000000 b44fb4ee 00000000 3d64bc2f .....O.. ....=d./
000000c9 000000cd ........
c33158 128 BLDCL 27 0 c33130 100030 8 8 4 0 _temptable
00000080 00000020 00100000 0000001b ....... ........
00c33130 b44fb4ee 00000000 00100030 ..10.O.. .......0
00000008 00000008 00040021 63737364 ........ ...!cssd
62000000 00000001 ffffffff 000b2669 b....... ......&i
6e666f72 6d697800 5f74656d 70746162 nformix. _temptab
6c650000 c635c018 00000065 6e5f5553 le...5.. ...en_US
2e383139 00000002 c6368d9c 006f8240 .819.... .6...o.@
00000001 00000001 ffffffff 0000a100 ........ ........

addr len type xid id link
c331d8 36 CHALLOC 27 0 c33158 126de3 8
00000024 00000033 00100000 0000001b ...$...3 ........
00c33158 b44fb4ef 00000000 00126de3 ..1X.O.. ......m.
00000008 ....
c331fc 40 PTEXTEND 27 0 c331d8 100030 7 126de3
00000028 00000032 00100000 0000001b ...(...2 ........
00c331d8 b44fb4f0 00000000 00100030 ..1..O.. .......0
00000007 00126de3 ......m.
c33224 36 COMMIT 27 0 c331fc 08/22/2002 15:55:51
00000024 00000002 00100000 0000001b ...$.... ........
00c331fc b44fb4f4 00000000 3d64bc2f ..1..O.. ....=d./
3d64bc2f =d./
c33248 40 BEGIN 27 280 0 08/22/2002 15:55:52 205 informix
00000028 01180001 00000000 0000001b ...(.... ........
00000000 b44fb7c9 00000000 3d64bc30 .....O.. ....=d.0
000000c9 000000cd ........
c33270 32 ERASE 27 0 c33248 100030
00000020 0000000d 00100000 0000001b ... .... ........
00c33248 b44fb7c9 00000000 00100030 ..2H.O.. .......0

addr len type xid id link
c33290 36 CHFREE 27 0 c33270 126de3 8
00000024 00000034 00100000 0000001b ...$...4 ........
00c33270 b44fb7c9 00000000 00126de3 ..2p.O.. ......m.
00000008 ....
c332b4 36 COMMIT 27 0 c33290 08/22/2002 15:55:52
00000024 00000002 00100000 0000001b ...$.... ........
00c33290 b44fb7cc 00000000 3d64bc30 ..2..O.. ....=d.0
3d64bc30 =d.0

Thanks for your attention.

Regards,
Shriyan

 

[shkk13]

Hi Shriyan

I created an user called &quot;guest&quot; at OS layer and gave connect privilege to my database and fired the sub-quey you specified using guest login. I followed the steps you specified and at the end I noticed the logical log usage increased by 0.06% (each log is of size 7500). The output of onlog is below:

onlog -n 1760 -u guest

Informix Dynamic Server Logical Log display
Software Serial Number XXX#X999999
Copyright (C) 1987-1998 Informix Software, Inc.

log number: 1760.

addr len type xid id link
dbf670 104 CKPOINT 1 1 19ab018 3
begin xid id addr user
181f2f0 56 CKPOINT 1 0 dbf670 1
begin xid id addr user
1820240 40 BEGIN 20 1760 0 08/26/2002 12:08:27 242 guest
1820268 128 BLDCL 20 0 1820240 1000a7 8 8 4 0 _temptable
18202e8 36 CHALLOC 20 0 1820268 127380 8
182030c 40 PTEXTEND 20 0 18202e8 1000a7 7 127380
1820334 36 COMMIT 20 0 182030c 08/26/2002 12:08:27
182058c 40 BEGIN 20 1760 0 08/26/2002 12:08:27 242 guest
18205b4 32 ERASE 20 0 182058c 1000a7
18205d4 36 CHFREE 20 0 18205b4 127380 8
18205f8 36 COMMIT 20 0 18205d4 08/26/2002 12:08:27

Surprising to know this. I'm unable to figure-out what's happening. Can any other Informix DBA/Experts or IBM put some light on this?

Rgds
jim