MVSFORUMS.com

Crox · Beginner Joined: 29 May 2004 Posts: 52 Topics: 9

Hi,

I want to do a query in DB2 to find special tokens like the ISPF EDIT p'.'.

It is about finding them in a separate character field and count how many rows are found with a certain column that contains p'.' tokens.

Is this possible and if so, how?

Regards,

Crox

vivek1983 · Intermediate Joined: 20 Apr 2006 Posts: 222 Topics: 24

Crox,

Try this: I am not sure of the HEX value for " P'.' ".

Nic Clouston · Posted: Fri Oct 12, 2007 1:57 pm Post subject:

POSTED IN ANOTHER PLACE (FORUM) - naughty:evil:
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

dbzTHEdinosauer · Posted: Fri Oct 12, 2007 1:58 pm Post subject:

Crox,

you did not like the answer given in the other forum?

Vivek,

Crox is looking for a short cut like ISPF FIND P'.' which looks for all unprintable characters. In ISPF for Change, Find, and exclude: P'=' is for any char. P'<' is lower case, > is upper case, etc....

When you are in an edit session (or view), PF1, then look at the write-up for FIND Special Characters, selection 1.

Unfortunately, db2 does not have Regular Expressions, which in could work for those languages that support Regular Expressions.
_________________
Dick Brenholtz
American living in Varel, Germany

Crox · Beginner Joined: 29 May 2004 Posts: 52 Topics: 9

It would be nice to get more than one answer. There are a lot of smart collegues here and I like to get all the answers.... Smile

The trouble is that the query is not on a very small database. I am afraid that it will run for hours, perhaps even for days. So.... I even think about using some software (edit macro or sort) on the output of a query that only selects the right fields.

Crox · Beginner Joined: 29 May 2004 Posts: 52 Topics: 9

Thank you Vivek!

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

Hello Crox,

unfortunately, the answer of Vivek is useless in the context of your question, and the post written by Dick contains the correct facts.

I wonder, how collecting lots of answers can improve the true one?

regards
Christian

Crox · Beginner Joined: 29 May 2004 Posts: 52 Topics: 9

well.... I hoped for one good answer that is usable. It is a one-time query and it has to be done without programming, using only stand software.... so... a simple query combined with an ispf edit macro or a SORT application can be used. It is a huge amount of data to process so that can also be a problem. Of course it is possible to check a single character within a string that it is between certain values, but that will be too much time consuming. I know some sql functions, but not all of them so I hoped to meet the real wizard here.... But thanks again for your ideas.

jsharon1248 · Posted: Wed Oct 17, 2007 8:23 am Post subject:

As long as you're only interested in identifying rows that have a specific column with an unprintable character (not the actual position of the p'.' character within the column), you can use the TRANSLATE function. Set up a predicate where you use TRANSLATE to convert unprintable characters to spaces and compare that to the original column value, then select the rows where they're different. I set this up for hex values 00 through 0F. You'd need to expand the list and add a space to the 'to-string' for each hex value added.

jsharon1248 · Posted: Wed Oct 17, 2007 8:25 am Post subject:

16 spaces in the 'to-list'

Crox · Beginner Joined: 29 May 2004 Posts: 52 Topics: 9

Thank you jsharon, this I will try