MVSFORUMS.com :: View topic - Searching by character string in SYSIBM.SYSSTMT table

Searching by character string in SYSIBM.SYSSTMT table

through

#1: Searching by character string in SYSIBM.SYSSTMT table Author: mvsmlk,

Posted: Fri Dec 18, 2015 3:39 pm
—
Hello all,

I am perplexed by this. Perhaps it's Friday and I am missing something.
I am attempting to search the TEXT column of table SYSIBM.SYSSTMT looking for a string LIKE '%TAIA%' or LIKE '%TASA%'. My query returns no hits. However, when I query the TEXT column of SYSIBM.SYSSTMT without a WHERE clause, I see these strings in the TEXT column. Can someone explain what might be going on?

Here is my query:

Code:

SELECT NAME, TEXT
FROM SYSIBM.SYSSTMT
WHERE
(
TEXT LIKE '%TAIA%'
OR
TEXT LIKE '%TASA%'
)
ORDER BY NAME
;

and TEXT column (Row 3) shows:

Code:

3_| DECLARE ADB2V . TAIAEMH TABLE ( PARTITION_MONTH CHAR ( 2 ) NOT NULL

So why does the query return no hit?
Thanks everyone.

#2: Author: kolusu, Location: San Jose

Posted: Fri Dec 18, 2015 3:57 pm
—
mvsmlk,

Unless I am missing something, the syntax should be untested code

Code:

SELECT NAME
,TEXT
FROM SYSIBM.SYSSTMT
WHERE TEXT LIKE '%TAIA%'
OR TEXT LIKE '%TASA%'
ORDER BY NAME
;

#3: Author: mvsmlk,

Posted: Fri Dec 18, 2015 4:06 pm
—
Thank you for your quick response.

I just ran the code you supplied...
Still no hits...

Code:

PAGE 1
***INPUT STATEMENT:
SELECT NAME
,TEXT
FROM SYSIBM.SYSSTMT
WHERE TEXT LIKE '%TAIA%'
OR TEXT LIKE '%TASA%'
ORDER BY NAME;
SUCCESSFUL RETRIEVAL OF 0 ROW(S)

#4: Author: kolusu, Location: San Jose

Posted: Fri Dec 18, 2015 5:42 pm
—

mvsmlk wrote:

Thank you for your quick response.

I just ran the code you supplied...
Still no hits...

well are you running this query in spufi or a program? if it is a program make sure it is looking at the production and not the test database. Did you rebind?

Ideally I would first test out the query in SPUFI/QMF and see if it is working there.

Second I would run the query

Code:

SELECT HEX(TEXT)
FROM SYSIBM.SYSSTMT

and I would see if the content in that column is valid hex character values.

#5: Author: mvsmlk,

Posted: Fri Dec 18, 2015 7:01 pm
—
An excellent suggestion to run using HEX(TEXT), as the query now seems to return rows, whereas using a CHAR compare it did not return rows....Wonder why. Any idea?
Does the fact that the TEXT column may be in UNICODE having anything to do with it?
Thanks again for your help.

By the way, you wouldn't happen to know how I can get rid of these SQLWARNING statements, would you?

Code:

PAGE 1
***INPUT STATEMENT:
SELECT NAME
,HEX(TEXT) AS HEX_TEXT
FROM SYSIBM.SYSSTMT
WHERE
HEX(TEXT) LIKE '%E3C1C9C1%' ==> instead of LIKE '%TAIA%'
OR
HEX(TEXT) LIKE '%E3C1E2C1%' ==> instead of LIKE '%TASA%'
ORDER BY NAME;
SQLWARNING ON SELECT COMMAND, FETCH FUNCTION
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 01004 SQLSTATE RETURN CODE
DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION
DSNT417I SQLWARN0-5 = W,W,,,, SQL WARNINGS
DSNT417I SQLWARN6-A = ,,,, SQL WARNINGS

etc...

Code:

| HEX_TEXT |
---------------------------------------------------------------------------------------------------------------------------+
1_| 0000042900000421C4C5C3D3C1D9C540C1C4C2F2E5404B40E3C1C9C1C5D4C840E3C1C2D3C5404D40D7C1D9E3C9E3C9D6D56DD4D6D5E3C840C3C8C1D9 |

,. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

#6: Author: kolusu, Location: San Jose

Posted: Fri Dec 18, 2015 9:56 pm
—
mvsmlk,

Please use CODE tags. I have been editing your posts to add them. Take a look at this post and see how you can add code tags.

http://www.mvsforums.com/helpboards/viewtopic.php?p=19031#19031

mvsmlk wrote:

An excellent suggestion to run using HEX(TEXT), as the query now seems to return rows, whereas using a CHAR compare it did not return rows....Wonder why. Any idea?
Does the fact that the TEXT column may be in UNICODE having anything to do with it?
Thanks again for your help.

You haven't answered me as to how you are running the query so far. Based on your output I am guessing that you are running DSNTEP2 in batch. So I guess there is a mismatch in your CCSID defined for DSNTEP2(batch) and your Db2 table and that could be the reason as to why you couldn't find the values for character search.

mvsmlk wrote:

By the way, you wouldn't happen to know how I can get rid of these SQLWARNING statements, would you?

I am not a big fan of DSNTEP2 at all. Agreed that it gives you formatted data of unprintable characters , it has lots of limitations such as the page width and extra garbage it prints out in the report at the beginning and at the end.

I would suggest that you use DSNTIAUL and you would get the raw data as is from the table in to the DD named SYSREC00

Check this link which explains in detail about How to run sample programs DSNTIAUL, DSNTIAD, and DSNTEP2

http://www.mvsforums.com/helpboards/viewtopic.php?p=31550#31550

Hope this helps...

Cheers

Kolusu

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1