Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Compare similar view outputs

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Fri Mar 12, 2010 7:46 pm    Post subject: Compare similar view outputs
Reply with quote

Hi All,
I am doing a re-engineering project. The reenegineering involved some data base changes also. So approach followed was to create a mirror database, and run the new software on that shadow database.

To know the effectiveness of the new software, it is planned to have a do a reconcillation of two databases with same set of inputs. The database definition difference are hidden using views.

Plan to unload data from the two databases useing views (using DSNTIAUL, as unload does not work with views) and comapre the output.

I have two questions
1. How i can add a 'header' in the unlaod using views ( with DSNTIAUL) ? I want a header record which will identify the table.

2. How I can compare the two unlaod files for a table, so that I am able to tell which column values are not same for the two tables (belonging to original and mirror DB ?

regards
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 12, 2010 9:43 pm    Post subject:
Reply with quote

Hello,

Before the unload, create a file (with the same dcb info as the unload creates) that contains the header. Then MOD the unload onto the file that contains the header.

To compare the unload files, you could run SUPERC in batch.
Back to top
View user's profile Send private message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Mon Mar 15, 2010 3:30 pm    Post subject: Reply to: Compare similar view outputs
Reply with quote

Hi Dick,
In SUPERC can I be able to provide the column name for the mismatched column ? I want that I should be able to pin point the coplumn which is getting wrongly populated.

To over come the header issue, I CONCATED the table name as first columns in view.

Thanks
Rohit
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Mar 15, 2010 7:43 pm    Post subject:
Reply with quote

Hello,

Quote:
In SUPERC can I be able to provide the column name for the mismatched column
Not that i'm aware of. . . I've found that if i identify mis-matches at the record level, i rarely need to show differences at the field level. When needed, i look at the 2 mis-matched records online.
Back to top
View user's profile Send private message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Fri Mar 26, 2010 5:53 pm    Post subject: Reply to: Compare similar view outputs
Reply with quote

Hi Dick,

I am still sruggling to find a solution to the issue. One question, regarding the following

Quote:
Before the unload, create a file (with the same dcb info as the unload creates) that contains the header. Then MOD the unload onto the file that contains the header.


I have all the columns information int he SYSPUNCH.

(
Code:
                                                                   
INSTRUMENTID                           POSITION(       1         )   
CHAR(                     24) ,                                     
INSTRUMENTFITYP                        POSITION(      25         )   
CHAR(                      2) ,                                     
FUNCTTYPE                              POSITION(      27         )   
CHAR(                      4)                                       


How can I use this syspunch to create the header which i will append at the top of the unlaod file ?
The out put will look like

(header) INSTRUMENTID INSTRUMENTFITYP FUNCTTYPE
(unlaod) XXXXXX YY UUUU

Regards
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 26, 2010 9:09 pm    Post subject:
Reply with quote

Hello,

Quote:
How can I use this syspunch to create the header which i will append at the top of the unlaod file ?
Use some utility or some program code to read those records and generate the header from the column names. Be careful that the header does not exceed the length of all of the data. . .

Something that would influence how much actual work i would put into this is how many differences are to be expected. If there is supposed to be no (or very few) differences, i would not put so much effort into identifying them by some sophisticated process. . .
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 685
Location: Earth

PostPosted: Fri Mar 26, 2010 11:13 pm    Post subject:
Reply with quote

Quote:
How i can add a 'header' in the unlaod using views ( with DSNTIAUL) ? I want a header record which will identify the table

You could create your header using simple sql like below in first step and concat with your unload to achieve desired result.
Code:
SELECT 'INSTRUMENTID' 'INSTRUMENTFITYP' 'FUNCTTYPE' FROM SYSIBM.SYSDUMMY1

Or If you are looking for a more generic way then get the column names for a table using below query before your actual unload step.
Code:
SELECT NAME FROM SYSIBM.SYSCOLUMNS
 WHERE TBNAME = '<your table>'
AND TBCREATOR = '<qualifier>'     

This will give you the column names in a dataset.
Then use a DFSORT trick pdf to combine these column names on 1 row.
(Refer to the famous 'Smart DFSORT Tricks' By 'THE Frank')

Concatenate this with your actual table unload.

I won't go into the efficiency of this solution but it should give you the desired output.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1746
Location: Bloomington, IL

PostPosted: Fri Mar 26, 2010 11:26 pm    Post subject:
Reply with quote

dick scherrer wrote:
I've found that if i identify mis-matches at the record level, i rarely need to show differences at the field level.

Perhaps we're thinking of different applications (in the non-IT sense), but what if a bit flag is set wrongly in every record?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 26, 2010 11:41 pm    Post subject:
Reply with quote

Hello,

Quote:
but what if a bit flag is set wrongly in every record?
Yup. That would be identified at a very quick glance and should require one "global" fix. . . I see no need to identify this type of "difference" with some elaborate comparison/report tool.

If there were 10 million records and there 10,000 differences in 500 fields, then the field-level presentation might be worth developing. . .

Just MHO icon_smile.gif
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1746
Location: Bloomington, IL

PostPosted: Sat Mar 27, 2010 2:19 am    Post subject:
Reply with quote

Well, perhaps the SAs in this shop are a bit less experienced than you icon_wink.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts COMPARE command sivatechdrive TSO/ISPF 7 Fri Sep 16, 2016 4:31 pm
No new posts Icetool or Joinkeys example to compar... ishant chauhan DFSORT/ICETOOL 21 Sat Aug 20, 2016 2:40 am
No new posts 24x7 online logs using DSN1LOGP (simi... SRICOBSAS DB2 3 Thu Aug 18, 2016 11:52 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us