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
 

 

Logic needed in replace of SUBSTR function

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

New User


Joined: 19 Oct 2007
Posts: 37
Location: chennai

PostPosted: Mon Aug 05, 2013 11:25 am    Post subject: Logic needed in replace of SUBSTR function
Reply with quote

Hi,

In our cobol program we are doing a join between table a and table b.

Declare d-id cursor with hold for
select deal,e.code from table a,table b where
substr(a.unit_id,5,10) = b.element_code

the table b has element_code which is 10 character and in table a the unit_id is 14 character.

our dba had reviewed and given a comment saying substr needs to be replaced.

could anyone suggest a better option for this

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

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1537
Location: Andromeda Galaxy

PostPosted: Mon Aug 05, 2013 11:53 am    Post subject:
Reply with quote

I would suggest you to first understand your DBA's suggesion
Back to top
View user's profile Send private message
karthik3883

New User


Joined: 19 Oct 2007
Posts: 37
Location: chennai

PostPosted: Mon Aug 05, 2013 11:56 am    Post subject: Reply to: Logic needed in replace of SUBSTR function
Reply with quote

DBA hasnt suggested anything,he informed to check for alternative logic

Regards
Karthik
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1537
Location: Andromeda Galaxy

PostPosted: Mon Aug 05, 2013 12:05 pm    Post subject:
Reply with quote

I wanted you to understand why he had asked you to change the logic.

Did you try to understand that or asked him why he said that?
Back to top
View user's profile Send private message
karthik3883

New User


Joined: 19 Oct 2007
Posts: 37
Location: chennai

PostPosted: Mon Aug 05, 2013 12:08 pm    Post subject: Reply to: Logic needed in replace of SUBSTR function
Reply with quote

The number of records in table a is huge.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1537
Location: Andromeda Galaxy

PostPosted: Mon Aug 05, 2013 2:05 pm    Post subject:
Reply with quote

How many records are there in both the tables and what are you trying to achieve after doing a join on select?

Are the fields substr(a.unit_id,5,10) = b.element_code indexed?

How many records do you expect after join?
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Mon Aug 05, 2013 8:56 pm    Post subject:
Reply with quote

Quote:
Are the fields substr(a.unit_id,5,10) = b.element_code indexed?

Even if unit_id was indexed, it won't be used as the substr expression is not using first few bytes of column.

@OP,
If you really need to use this SUBSTR, you might want to look into 'Expression Based Indexes'.
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 What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
This topic is locked: you cannot edit posts or make replies. SORT trick needed bshkris SYNCSORT 6 Tue May 02, 2017 4:35 am
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm


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