Sunday, November 29, 2009

The Shy Objects

Hi

This post will be a bit different from the previous ones. No big experiments or useful tips, just an issue you will probably never encounter. I stumbled across it by accident while I was writing a script (that will be published in the near future).

I have some experience with SQL Server, and there you can find many strange things you would not expect, stuff that seems like the QA team did not think about.
I never thought I will encounter such issue with DB2, but then again, maybe I'm over reacting. This is not a bug, just a scenario I would not expect to "work by design".

So what am I talking about? Have you ever tried to create a table with no creator? The obvious question is "why would I?", but, this is in the sake of science.

I came across a record in my SYSIBM.SYSTABLES table which had a blank creator, that is, I got the records by running:

Select creator,name
from sysibm.systables
where creator = ''

I was astonished to find two such tables so I tried to create such a table myself. This is the output from my SPUFI session:

---------+---------+---------+---------+---------+---------+----
SET CURRENT SQLID='';
---------+---------+---------+---------+---------+---------+----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
SELECT CURRENT SQLID FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+----

---------+---------+---------+---------+---------+---------+----

DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+----
CREATE TABLE MOMI (COL1 INT);
---------+---------+---------+---------+---------+---------+----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
SELECT * FROM DB2T.SYSIBM.SYSTABLES
WHERE CREATOR=''
WITH UR;
---------+---------+---------+---------+---------+---------+---------+---------+
NAME CREATOR TYPE DBNAME TSNAME DBID OBID COLCOUNT
---------+---------+---------+---------+---------+---------+---------+---------+
MOMI T DSNDB04 MOMI 4 1011 1
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+

But now for the big question, how would I query this table without setting my current sqlid to '' ?

Well, this did the trick
SELECT * FROM " ".MOMI;

Regarding what happens when you perform
CREATE TABLE " " (COL1 INT)

I'll let you check it yourself...

That’s it for now…

1 comment:

  1. Interesting Post... I will try it tomorrow @work.

    ReplyDelete