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 

A scan of the production database on particular segment

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


Joined: 11 Mar 2009
Posts: 58
Topics: 25

PostPosted: Fri Feb 17, 2017 2:15 am    Post subject: A scan of the production database on particular segment Reply with quote

A scan of the production database is needed on the particular segments. We have a condition that If the SYSTOLIC entry is numeric, then the associated DIASTOLIC should be numeric as well. Other way around as well. The requirement is to check whether there are any values not satisfying this condition in database. The particular part is defined as below.

Code:

35   3 BLOOD-PRESS-READINGS-SMH                            AN    104    12                     
36    4 FIRST-RDNG-SMH                                     AN    104     4                     
37     5 1ST-SYSTOLIC-SMH                                  PD    104     2      0             
38     5 1ST-DIASTOLIC-SMH                                 PD    106     2      0             
39    4 SECOND-RDNG-SMH                                    AN    108     4                     
40     5 2ND-SYSTOLIC-SMH                                  PD    108     2      0             
41     5 2ND-DIASTOLIC-SMH                                 PD    110     2      0             
42    4 THIRD-RDNG-SMH                                     AN    112     4                     
43     5 3RD-SYSTOLIC-SMH                                  PD    112     2      0             
44     5 3RD-DIASTOLIC-SMH                                 PD    114     2      0             
45   3 FILLER                                              AN    116    10                     


Is there any way to get the list of records not matching the criteria mentioned into a file. Using JCL or Fileman Batch or easytrieve. Please give sample jcl/program.
_________________
Thanks
TVSSV
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri Feb 17, 2017 11:50 am    Post subject: Reply with quote

tvssv,

You say segments and database in the same sentence. Do you have a DB2 table or IMS database?

If it is a DB2 table, you can use SQL to translate the decimal fields to null value and see if you get an empty string.

Or alternatively you can unload the data into a sequential file and use utility like DFSORT to filter out the records with NUM test on INCLUDE COND.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
tvssv
Beginner


Joined: 11 Mar 2009
Posts: 58
Topics: 25

PostPosted: Mon Feb 20, 2017 5:40 am    Post subject: Reply with quote

Hi

The database is IMS DB. Now I have retrieved the required segment to a file. The file is in VB format.

We have a condition that If the SYSTOLIC entry is numeric, then the associated DIASTOLIC should be numeric as well. Other way around as well. The requirement is to check whether there are any values not satisfying this condition. The particular fields are defined as below.

Code:

35   3 BLOOD-PRESS-READINGS-SMH                            AN    104    12                     
36    4 FIRST-RDNG-SMH                                     AN    104     4                     
37     5 1ST-SYSTOLIC-SMH                                  PD    104     2      0             
38     5 1ST-DIASTOLIC-SMH                                 PD    106     2      0             
39    4 SECOND-RDNG-SMH                                    AN    108     4                     
40     5 2ND-SYSTOLIC-SMH                                  PD    108     2      0             
41     5 2ND-DIASTOLIC-SMH                                 PD    110     2      0             
42    4 THIRD-RDNG-SMH                                     AN    112     4                     
43     5 3RD-SYSTOLIC-SMH                                  PD    112     2      0             
44     5 3RD-DIASTOLIC-SMH                                 PD    114     2      0             
45   3 FILLER                                              AN    116    10                     


The fields are in packed decimal format. Could you please provide the sort cards or any other way to check the fields and retrieve those records which are matching the criteria. That is if any of the fields are not numeric.

Thanks[/code]
_________________
Thanks
TVSSV
Back to top
View user's profile Send private message
misi01
Advanced


Joined: 02 Dec 2002
Posts: 616
Topics: 171
Location: Stockholm, Sweden

PostPosted: Mon Feb 20, 2017 9:17 am    Post subject: Reply with quote

So, if I understand you correctly, the fact that the data is in an IMS database is (sort of) irrelevant. The data could just as easily be in a simple, sequential file and the same requirement would be true.

For example, you could write a Rexx script to access the database, but maybe you don't have the in-shop competence to do this.

I found what seemed to be another option here http://www.mvsforums.com/helpboards/viewtopic.php?p=9278&sid=db92839db869f073883043ce05f794c0

Basically, there are a number of ways of doing this which will depend on your shop and what s/w they have installed.
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
tvssv
Beginner


Joined: 11 Mar 2009
Posts: 58
Topics: 25

PostPosted: Mon Feb 20, 2017 10:08 am    Post subject: Reply with quote

Hi

Thank You.

I have downloaded the database to flat VB file. I need to check whether all the fields (PD) are numeric or not. If I found any non numberic data in the specified fields (please see the fields mention above) I need to write into new file. Please provide sort instructions to do this.

Thanks
_________________
Thanks
TVSSV
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Mon Feb 20, 2017 12:36 pm    Post subject: Reply with quote

tvssv wrote:
Hi

Thank You.

I have downloaded the database to flat VB file. I need to check whether all the fields (PD) are numeric or not. If I found any non numberic data in the specified fields (please see the fields mention above) I need to write into new file. Please provide sort instructions to do this.

Thanks


tvssv,

You mean UNLOADED right? Please use the proper terms.

Now that you have unloaded the data into a VB file, the position of the data is displaced by 4 bytes as the first 4 bytes contain the RDW. so use the following JCL to filter the records. It will write 2 files, NUM and NOTNUM
Code:

//STEP0100 EXEC PGM=SORT             
//SYSOUT   DD SYSOUT=*               
//SORTIN   DD DISP=SHR,DSN=Your Input VB file of IMS database
//NUM      DD SYSOUT=*   
//NOTNUM   DD SYSOUT=*
//SYSIN    DD *                                       
  OPTION COPY                                         
  OUTFIL FNAMES=NUM,                                   
  INCLUDE=((108,2,PD,EQ,NUM,AND,110,2,PD,EQ,NUM),AND, 
           (112,2,PD,EQ,NUM,AND,114,2,PD,EQ,NUM),AND, 
           (116,2,PD,EQ,NUM,AND,118,2,PD,EQ,NUM))     

  OUTFIL FNAMES=NOTNUM,SAVE                           
//*

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
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