Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Control Report Generation

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

New User


Joined: 28 May 2009
Posts: 10
Location: Hyderabad

PostPosted: Fri May 29, 2009 5:18 pm    Post subject: Control Report Generation
Reply with quote

Hi All,

I need to prepare a PLI program which would generate a control report in the spool region. It even icludes the DB2 qeuries. Below are the specifications:

1. My job includes selection of from a table1 where column1 = value1,value2,value3
2. Update the selected rows with column1 values as
value1 to value4
value2 to value5
value3 to value6

Report shld say as below:
Code:

************** START OF THE REPORT ********************
Name of the table : TABLE1
Number of rows selected (A): (No. fo rows from 1st step above)
Number of rows updated (B): (No. of rows updated from 2nd step above)
Number of rows bypassed (C): (if any)
Report Validation : A = B + C

Old Column Value       New Column Value         No. of Rows Updated
value1                         value4                              15
value2                         value5                              45
value3                         value6                              90

Rows Bypassed:
Column : column1
Value : valueX
=> No record found for this column value

****************** END OF REPORT ********************


Even a pesudocode or algorithm wud work for me....
Back to top
View user's profile Send private message

kumarcoolboy

New User


Joined: 28 May 2009
Posts: 10
Location: Hyderabad

PostPosted: Fri May 29, 2009 5:28 pm    Post subject:
Reply with quote

am using cursors for the queries......
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 May 29, 2009 8:35 pm    Post subject:
Reply with quote

Hello,

So, what is the question?

If you want to record operational statistics, put whatever accumulators you want in the code that issues the queries. Some systems even have tables defined to store such information. Your code would insert rows nto these statistics tables as well as the tables that support the business function.
Back to top
View user's profile Send private message
kumarcoolboy

New User


Joined: 28 May 2009
Posts: 10
Location: Hyderabad

PostPosted: Sat May 30, 2009 1:50 pm    Post subject:
Reply with quote

hi Dick,

Am in search of such a query which would gimme those details........... for example, if I 'm using the below query
Code:

SELECT COL1
FROM TABLE1
WHERE CONDTION1, CONDITION2
ORDER BY COL1;

Can you help me modify in such a way that my output comes in the below fashion....
Code:

Output:
Col Value      No. of Rows
Value1      15
Value2      20
Value3      5
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: Sat May 30, 2009 3:32 pm    Post subject:
Reply with quote

Hello,

What you ask about now is not the same as what was requested earlier. . .

Once you post a consistent requirement definition, someone may be able to offer a suggestion.

How will the output be used?
Back to top
View user's profile Send private message
kumarcoolboy

New User


Joined: 28 May 2009
Posts: 10
Location: Hyderabad

PostPosted: Sat May 30, 2009 3:52 pm    Post subject:
Reply with quote

Hi Dick,

I have asked the same query, all my query needs to do is give me the count of the distinct row values. I can program accordingly.
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: Sat May 30, 2009 4:04 pm    Post subject:
Reply with quote

Hello,

Quote:
I have asked the same query, all my query needs to do is give me the count of the distinct row values
They don't look the same to me - of course it is just now sunup and i'm slow this morning icon_confused.gif
Quote:
Can you help me modify in such a way that my output comes in the below fashion....

Code:
Output:
Col Value      No. of Rows
Value1      15
Value2      20
Value3      5

Quote:
Report shld say as below:
Code:

************** START OF THE REPORT ********************
Name of the table : TABLE1
Number of rows selected (A): (No. fo rows from 1st step above)
Number of rows updated (B): (No. of rows updated from 2nd step above)
Number of rows bypassed (C): (if any)
Report Validation : A = B + C

Old Column Value       New Column Value         No. of Rows Updated
value1                         value4                              15
value2                         value5                              45
value3                         value6                              90

Rows Bypassed:
Column : column1
Value : valueX
=> No record found for this column value

****************** END OF REPORT ********************


I believe you can get the "count by value" result with a simple select using a COUNT and an ORDER BY. The other may take more work - and not all of it query code.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2287
Location: @my desk

PostPosted: Sat May 30, 2009 7:17 pm    Post subject:
Reply with quote

Quote:
Am in search of such a query which would gimme those details
I am not a PL/I expert, but can't this "counting" statistics stuff be done separately in your program with a couple of accumulators as suggested above? Why do you want to do this in "query" itself? And I dont think it's desirable to code an "all-in-one" sql evenif it is doable. Just a thought.
Back to top
View user's profile Send private message
kumarcoolboy

New User


Joined: 28 May 2009
Posts: 10
Location: Hyderabad

PostPosted: Tue Jun 02, 2009 10:01 am    Post subject: Reply to: Control Report Generation
Reply with quote

Thank you verh much all of you for your help people. icon_smile.gif icon_biggrin.gif
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: Tue Jun 02, 2009 10:09 am    Post subject: Reply to: Control Report Generation
Reply with quote

You're welcome - do you now have a process that does what you need?

It may help others if you post your working solution icon_wink.gif

d
Back to top
View user's profile Send private message
kumarcoolboy

New User


Joined: 28 May 2009
Posts: 10
Location: Hyderabad

PostPosted: Tue Jun 02, 2009 10:50 am    Post subject:
Reply with quote

I am using temp variables to do that...... here is what am doing.....

Code:

DEFINE C1 CURSOR FOR SELECT;
END SELECT;

OPEN C1;
END OPEN;

FETCH C1 INTO :VAR;
IF SQLCODE = 0 THEN
   READ_CNT = READ_CNT +1;
END FETCH;

COND1: IF READ_CNT = 1 THEN
   PRES_VALUE = VAR;
   PREV_VALUE =  PRES_VALUE;
ELSE
   PRES_VALUE = VAR;
END;

IF PREV_VALUE = PRES_VALUE
   CALL FETCH( );
ELSE
   DISPLAY VAR, READ_CNT;
   SET READ_CNT = 1;
   LOOP BACK TO COND1;
END;


Hope this is helpful for someone.......
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: Wed Jun 03, 2009 1:02 am    Post subject:
Reply with quote

Hello,

If that does what you need - cool icon_smile.gif

It does not look like it does what the "specs" called for. . . icon_confused.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 Control-M restart instruction sonaliingle CA Products 2 Tue Nov 28, 2017 5:05 pm
No new posts Copying part of the report using SARBCH Aedulla CA Products 1 Tue Oct 24, 2017 9:24 am
No new posts Report cost in CA-dispatch Nileshkul CA Products 3 Wed Jun 07, 2017 10:32 pm
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
No new posts PA02 - Programmtically control attent... dodithegreat IMS DB/DC 1 Thu Apr 06, 2017 8:33 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us