IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Logic needed in replace of SUBSTR function


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

Global Moderator


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

PostPosted: Mon Aug 05, 2013 11:53 am
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
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

Global Moderator


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

PostPosted: Mon Aug 05, 2013 12:05 pm
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
Reply with quote

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

Global Moderator


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

PostPosted: Mon Aug 05, 2013 2:05 pm
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: 691
Location: Earth

PostPosted: Mon Aug 05, 2013 8:56 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Replace HEX value with another HEX va... DFSORT/ICETOOL 8
No new posts How to replace a string dynamically i... CLIST & REXX 16
No new posts Need to split single line into two li... DFSORT/ICETOOL 7
No new posts Hex Value - vb file replace old date ... DFSORT/ICETOOL 14
No new posts DB2 - row_number function - Need 1st ... DB2 5
Search our Forums:

Back to Top