| View previous topic :: View next topic |
| Author |
Message |
mvsmlk
Beginner
Joined: 08 Oct 2015
Posts: 13
Topics: 6
|
 Posted: Fri Dec 18, 2015 3:39 pm Post subject: Searching by character string in SYSIBM.SYSSTMT table |
 |
|
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. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Dec 18, 2015 3:57 pm Post subject: |
|
|
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
; |
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mvsmlk
Beginner
Joined: 08 Oct 2015
Posts: 13
Topics: 6
|
| Posted: Fri Dec 18, 2015 4:06 pm Post subject: |
|
|
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) |
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Dec 18, 2015 5:42 pm Post subject: |
|
|
| 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.
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mvsmlk
Beginner
Joined: 08 Oct 2015
Posts: 13
Topics: 6
|
| Posted: Fri Dec 18, 2015 7:01 pm Post subject: |
|
|
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 |
,. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . |
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Dec 18, 2015 9:56 pm Post subject: |
|
|
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
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
