MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Searching by character string in SYSIBM.SYSSTMT table

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
mvsmlk
Beginner


Joined: 08 Oct 2015
Posts: 11
Topics: 4

PostPosted: Fri Dec 18, 2015 3:39 pm    Post subject: Searching by character string in SYSIBM.SYSSTMT table Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 11813
Topics: 74
Location: San Jose

PostPosted: Fri Dec 18, 2015 3:57 pm    Post subject: Reply with quote

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 - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
mvsmlk
Beginner


Joined: 08 Oct 2015
Posts: 11
Topics: 4

PostPosted: Fri Dec 18, 2015 4:06 pm    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 11813
Topics: 74
Location: San Jose

PostPosted: Fri Dec 18, 2015 5:42 pm    Post subject: Reply with quote

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 - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
mvsmlk
Beginner


Joined: 08 Oct 2015
Posts: 11
Topics: 4

PostPosted: Fri Dec 18, 2015 7:01 pm    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 11813
Topics: 74
Location: San Jose

PostPosted: Fri Dec 18, 2015 9:56 pm    Post subject: Reply with quote

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 - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group