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
 
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: 1561
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: 1561
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: 1561
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 USS: Find and replace string within a... vasanthz All Other Mainframe Topics 5 Thu Aug 02, 2018 4:01 am
No new posts Help needed with INSPECT yvalhe COBOL Programming 3 Thu May 31, 2018 1:10 am
No new posts Removing the period or dot from the v... gopinak COBOL Programming 6 Wed May 09, 2018 1:53 pm
No new posts Find & Replace string in CA-SORT mrgnndhmk CA Products 1 Fri Mar 30, 2018 12:58 am
No new posts Replace last name by first name anatol DFSORT/ICETOOL 10 Thu Mar 15, 2018 3:43 pm

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