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
 

 

More efficient: using SQL's or doing conversions in COBOL

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

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Nov 17, 2008 5:36 pm    Post subject: More efficient: using SQL's or doing conversions in COBOL
Reply with quote

Hi,

I have a query like this
Code:
SELECT  EMPNO ,
        ( FIRSTNME
        || ' '
        || MIDINIT
        || ' '
        || LASTNAME ) AS FULLNAME ,
        (
        CASE
                WHEN YEAR(CURRENT DATE           - HIREDATE) < 1
                THEN VARCHAR(MONTH( CURRENT DATE - HIREDATE ))
                        || ' MONTHS'
                ELSE VARCHAR(YEAR ( CURRENT DATE - HIREDATE ))
                        || ' YEARS'
        END ) AS EXP_IN_MONS_YRS ,
        JOB                      ,
        ( SALARY + BONUS + COMM ) AS GROSS
FROM    SHANDB.EMPLOYEE
WHERE   SEX = 'M'
ORDER BY YEAR(CURRENT DATE - HIREDATE) ASC ;


I want know what will be effcient & good for the application, doing conversions & calculations in COBOL program or using SQL functions.

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

Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Nov 17, 2008 6:30 pm    Post subject:
Reply with quote

Usage of case is ok. But I think you should try to reduce the calculations within the query. Ex.
Code:
CURRENT DATE           - HIREDATE
this can be easily and "efficiently" done using COBOL. All you need to do is store it in a host variable and mention in the SQL. This will reduce a burden from the Query.
Back to top
View user's profile Send private message
trilokdewangan

New User


Joined: 19 Jun 2008
Posts: 39
Location: USA

PostPosted: Mon Nov 17, 2008 6:34 pm    Post subject:
Reply with quote

Hi Sushanth,

I believe, Embedded SQL with COBOL program is efficient but is also depends on your requirement. Here you gave the query, which can also be written in COBOL program but, you need to install the program in production (if want production data) to get the output Instead you can run SPUFI in production since your query is not complicated.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Nov 17, 2008 6:48 pm    Post subject:
Reply with quote

Forgot to mention,
Quote:
The efficiency of the functions in SQL depend on the no. of rows that will be returned as a result of this query. So if the result table is too large, then it would be better to go for COBOL.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Nov 17, 2008 6:58 pm    Post subject:
Reply with quote

Guyz,

Bharath, YES. Year Calculations can be done in cobol with a few lines of code.

Trilok, Yes. This query can be executed in SPUFI and results can be easily got.

My question is entirely different. "What will be more efficient, cobol codings,calculations, conversion & decisions OR using SQL functions". B'cos i have seen many cobol codings using DB2 like selecting all the columns in table and performing calculations which could go upto tonnes & tonnes of coding. But, what i don't understand is those conversion & calculations can be easily be in SQL itself, which can reduce the amount of coding in cobol.

Quote:
The efficiency of the functions in SQL depend on the no. of rows that will be returned as a result of this query. So if the result table is too large, then it would be better to go for COBOL.

Because most of the select's have huge resultsets.
In this case, SQL ()'s can be only used in SPUFI.

As said above "efficiency of the ()'s in SQL depends on the no. of rows".
What kind of function()'s can be effcient like calculations()'s, text related()'s, conversion ()'s. And to what extent, will it be efficient.

Sushanth
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Nov 17, 2008 7:23 pm    Post subject:
Reply with quote

Quote:
But, what i don't understand is those conversion & calculations can be easily be in SQL itself, which can reduce the amount of coding in cobol.


This is to improve the performance of the query, which seems to be your objective. If you want these calculations to be done by SQL, then you are trading off the performance of the query for a few lines of COBOL code.
Such calcualations within the query will cause it to be non indexable.Indexable queries will give you optimal performances. So, Its not wise to make the query non indexable.

Coming to the functions part,

Use of functions for reducing the amount of COBOL code may be useful only if a handfull of rows are returned. If the number of rows is going to be large, then the amount of time taken by the SQL to execute the query will be more. In such cases, COBOL code will surely be efficient.

What I would say is, check the result set in realtime environment and then decide.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Nov 17, 2008 7:30 pm    Post subject:
Reply with quote

Yes bharath. I get it.

Thank You.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Nov 17, 2008 7:35 pm    Post subject:
Reply with quote

You're welcome.

BTW, was it part of a requirement or just a doubt?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Nov 17, 2008 7:39 pm    Post subject:
Reply with quote

Just a doubt, bharath.
I am very fond of SQL more than COBOL. That's WHY ?

Sushanth
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 Nov 17, 2008 10:02 pm    Post subject:
Reply with quote

Hello,

Quote:
I am very fond of SQL more than COBOL.
We tend to find a reason to use tools we like best. . . icon_smile.gif

Suggest you put many tools in your "toolbox", know how to use all of them well, and then choose the proper tool for the task at hand. There is no "one tool that does everything best".
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Nov 18, 2008 10:00 am    Post subject:
Reply with quote

Thank You dick,

Quote:
There is no "one tool that does everything best".

That's a very good quote

Sushanth
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
This topic is locked: you cannot edit posts or make replies. Cobol GUI (like Editor) Kala Mainframe COBOL Programming 14 Wed May 10, 2017 12:30 pm
No new posts IMS T-Pipe queue counts in a COBOL Pr... Siva NKK Kothamasu IMS DB/DC 0 Tue May 09, 2017 6:31 pm
No new posts COBOL Code Parsers and Lineage Establ... balimanja COBOL Programming 2 Tue May 02, 2017 3:30 am
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts COBOL Programming Sandpit? jodrisco COBOL Programming 6 Wed Apr 12, 2017 3:47 am


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