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: 1529
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: 1529
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: 1529
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: 685
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
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Replace repeated JCL with looping REXX prino CLIST & REXX 3 Thu Oct 13, 2016 5:30 am
No new posts Strange EXEC function call in z/VM Willy Jensen CLIST & REXX 3 Wed Oct 05, 2016 2:07 pm
This topic is locked: you cannot edit posts or make replies. Sort card to replace text using cross... Susanta SYNCSORT 32 Tue Sep 13, 2016 4:17 pm


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