qmf create view question

Tina Hilton

qmf create view question
We have some views that were created by qmf and contain COMMIT WORK; in the
text column of sysviews. Does anyone know if this is how it's supposed to
be? I can put anything (even garbage) after the first semi-colon and it is
stored in sysviews. DSNTIAD and DSNTEP2 doesn't include anything after the
first semi-colon in sysviews, but if it comes from QMF it will include
whatever's there. I reported it to IBM as a problem, but they just told me
to reroute it as a QMF usage question. I guess that must be their way of
saying it's working as designed, but I can't find anything in the books
about it working this way.

Here's my QMF query:

CREATE VIEW THILTON.TESTQ2
( COL1 )
AS SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1
;
--COMMIT WORK
!!!!!!!!!!JUNK HERE!!!!!!!!!!!!!!
;

SYSVIEW's TEST column contains this:

' CREATE VIEW THILTON.TESTQ2 ( COL1 ) AS SELECT CURRENT DATE FROM
SYSIBM.SYSDUMMY1 ; !!!!!!!!!!JUNK HERE!!!!!!!!!!!!!! ; '


Let me know if anyone's heard of this "feature" before.


Tina Hilton
Database Analyst
arvato systems



teldb2kals

Re: qmf create view question
(in response to Tina Hilton)
Hi Tina,

If it is of any help, I tried it, and got the same result.

Also, it doesn't seem to depend on the semicolon. I tried the following SQL
in QMF in a single line, (note the word "JUNK" at the end), without any
semicolon, and found that it includes the word "JUNK" also in the SYSVIEWS
entry.

CREATE VIEW V1(COL1) AS SELECT NAME FROM SYSIBM.SYSTABLES JUNK

When I execute the above SQL using SPUFI or DSNTEP2, (with the semicolon
after the word "JUNK"), the same results. (the SYSVIEWS entry
includes "JUNK").

And, when I execute just the select query independently as

SELECT NAME FROM SYSIBM.SYSTABLES JUNK;

The query still gets executed, without complaining about the word "JUNK".

But the moment I add an extra junk word "JUNK1" there, as below,

SELECT NAME FROM SYSIBM.SYSTABLES JUNK JUNK1;

it complains

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "JUNK1". SOME SYMBOLS THAT
MIGHT BE LEGAL ARE: UNION

There were some more similar odd results, but I don't have all the outputs
now.

Regards,
Kals

On Mon, 6 Jan 2003 14:29:30 -0500, Tina Hilton
<[login to unmask email]> wrote:

>We have some views that were created by qmf and contain COMMIT WORK; in the
>text column of sysviews. Does anyone know if this is how it's supposed to
>be? I can put anything (even garbage) after the first semi-colon and it is
>stored in sysviews. DSNTIAD and DSNTEP2 doesn't include anything after the
>first semi-colon in sysviews, but if it comes from QMF it will include
>whatever's there. I reported it to IBM as a problem, but they just told me
>to reroute it as a QMF usage question. I guess that must be their way of
>saying it's working as designed, but I can't find anything in the books
>about it working this way.
>
>Here's my QMF query:
>
> CREATE VIEW THILTON.TESTQ2
> ( COL1 )
> AS SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1
>;
>--COMMIT WORK
>!!!!!!!!!!JUNK HERE!!!!!!!!!!!!!!
>;
>
>SYSVIEW's TEST column contains this:
>
>' CREATE VIEW THILTON.TESTQ2 ( COL1 ) AS SELECT CURRENT DATE FROM
>SYSIBM.SYSDUMMY1 ; !!!!!!!!!!JUNK HERE!!!!!!!!!!!!!! ; '
>
>
>Let me know if anyone's heard of this "feature" before.
>
>
>Tina Hilton
>Database Analyst
>arvato systems
>
>
>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Peter Backlund

Re: qmf create view question
(in response to teldb2kals)
In your first example "junk" is treated as a correlation name for
SYSIBM.SYSTABLES.

Compare this to the following

select name salary
from emp

which gives a result table with one column named "salary"
and having names as its data -funny!

SQL assumes teh as keyword

Peter

Teldb2kals wrote:

>Hi Tina,
>
>If it is of any help, I tried it, and got the same result.
>
>Also, it doesn't seem to depend on the semicolon. I tried the following SQL
>in QMF in a single line, (note the word "JUNK" at the end), without any
>semicolon, and found that it includes the word "JUNK" also in the SYSVIEWS
>entry.
>
>CREATE VIEW V1(COL1) AS SELECT NAME FROM SYSIBM.SYSTABLES JUNK
>
>When I execute the above SQL using SPUFI or DSNTEP2, (with the semicolon
>after the word "JUNK"), the same results. (the SYSVIEWS entry
>includes "JUNK").
>
>And, when I execute just the select query independently as
>
>SELECT NAME FROM SYSIBM.SYSTABLES JUNK;
>
>The query still gets executed, without complaining about the word "JUNK".
>
>But the moment I add an extra junk word "JUNK1" there, as below,
>
>SELECT NAME FROM SYSIBM.SYSTABLES JUNK JUNK1;
>
>it complains
>
>DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "JUNK1". SOME SYMBOLS THAT
> MIGHT BE LEGAL ARE: UNION
>
>There were some more similar odd results, but I don't have all the outputs
>now.
>
>Regards,
>Kals
>
>On Mon, 6 Jan 2003 14:29:30 -0500, Tina Hilton
><[login to unmask email]> wrote:
>
>
>
>>We have some views that were created by qmf and contain COMMIT WORK; in the
>>text column of sysviews. Does anyone know if this is how it's supposed to
>>be? I can put anything (even garbage) after the first semi-colon and it is
>>stored in sysviews. DSNTIAD and DSNTEP2 doesn't include anything after the
>>first semi-colon in sysviews, but if it comes from QMF it will include
>>whatever's there. I reported it to IBM as a problem, but they just told me
>>to reroute it as a QMF usage question. I guess that must be their way of
>>saying it's working as designed, but I can't find anything in the books
>>about it working this way.
>>
>>Here's my QMF query:
>>
>> CREATE VIEW THILTON.TESTQ2
>> ( COL1 )
>> AS SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1
>>;
>>--COMMIT WORK
>>!!!!!!!!!!JUNK HERE!!!!!!!!!!!!!!
>>;
>>
>>SYSVIEW's TEST column contains this:
>>
>>' CREATE VIEW THILTON.TESTQ2 ( COL1 ) AS SELECT CURRENT DATE FROM
>>SYSIBM.SYSDUMMY1 ; !!!!!!!!!!JUNK HERE!!!!!!!!!!!!!! ; '
>>
>>
>>Let me know if anyone's heard of this "feature" before.
>>
>>
>>Tina Hilton
>>Database Analyst
>>arvato systems
>>
>>
>>
>>
>>
>the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
>can
>
>
>
>
>
>

--

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+



teldb2kals

Re: qmf create view question
(in response to Peter Backlund)
Thanks, Peter. I did feel I was missing something simple and obvious, but
didn't realise what it was until after a few hours later.

Regards,
Kals

On Tue, 7 Jan 2003 08:35:58 +0100, Peter Backlund <[login to unmask email]>
wrote:

>In your first example "junk" is treated as a correlation name for
>SYSIBM.SYSTABLES.
>
>Compare this to the following
>
>select name salary
>from emp
>
>which gives a result table with one column named "salary"
>and having names as its data -funny!
>
>SQL assumes teh as keyword
>
>Peter
>