×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

why is it a SELECT statement writing logical log ?

thread404-341394

why is it a SELECT statement writing logical log ?

why is it a SELECT statement writing logical log ?

(OP)
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"

RE: why is it a SELECT statement writing logical log ?

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.

RE: why is it a SELECT statement writing logical log ?

(OP)
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

RE: why is it a SELECT statement writing logical log ?

Hi Shriyan

I created an user called "guest" 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close