We help IT Professionals succeed at work.

SCAN/REPLACE

bcloring08
bcloring08 asked
on
893 Views
Last Modified: 2010-04-21
I have a SQLSTMTSTR that has the entire SQL script and is built dynamically in the SQLRPGLEW program.  Once the build is complete, I have to SCAN for the WILDCARD CHARS "???"  and replace with the field name EMSCEM in ALL occurances in the SQLSTMTSTR.  I have to do this because there are multiple tables to join and the SCHEMA name has to precede EACH table name, so it will point to the correct DB on the UNIX server.
Comment
Watch Question

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

Hi bcloring08,

Is there a question here?


Kent

Author

Commented:
Yes, I need to be able to SCAN and REPLACE the "???"  in the SQL script and replace the "???" with  a field name EMSCEM that has the MDM DB name in there.

Here is the idea that I am trying. I don't know if it will process ALL occurances or just the first one though, since I can't connect to the DB yet to ry it out....

C                   Z-ADD     0             Position          3 0    
C                   eval      Position = %scan('???':SqlStmtStr)      
C                   If        Position <> 0                          
C                   eval      SqlStmtStr = %replace(EMSCEM:SqlStmtStr:
C                             position:1)                            
C                   endif                                            




i.e.
SPR_V (this is the MDM DB view) AL1 (is the table name Alias)
           FROM  ???.SPR_V AL1, ???.TIME_FSC_DY_V AL2, ???.CP_DLY_SLS_V AL6,
???.CP_INV_LCN_V AL8, ???.BSN_UN_PDT_A_V AL9, ???.PDT_CNSGN_IND_V AL10, ???.BSN_
UN_LCN_V AL11                                                                  
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
What's your host O/S?

Author

Commented:
HOST O/S      =   AS400 Db2-400  
TARGET O/S  =   Oracle MDM DB on Unix server
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

If you can move the source to the unix box, changing it from there is almost trivial.

sed 's/\?\?\?/EMSCEM/g' oldfilename > newfilename



Kent

Author

Commented:
Kdo,

     Unfortunately, because this is an As400 BATCH job, moving the source to the Unix system is not an option.  

    The goal of this project is to connect to the Unix MDM DB, extract the inventory and sales information in the tables there, return to the AS400 with the SQL statement built, then I load it to the 852 EDI transaction set and send the information to the trading partners.  I think the only 2 pieces I am missing is CONNECTING to the UNIX from the iSeries and then once the SQL statement is built (SQLSTMTSTR)  then I have to replace the wildcard chars (???) with the Schema name in multiple locations in the script.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

Dave's probably our most knowledgeable AS/400 geek, er guru.  Let's see if he can help.

I have no idea how you would connect to an Oracle database from the AS/400.  I don't even know if it can be done, but I suspect that it can.

If the data volume is modest, I'd consider firing up DB2 on your unix server and federating it to the AS/400 and the Oracle systems.  You can then run all of the SQL from the federated DB2 server and most of this will just go away.


Kent
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you that worked perfectly. Now all I have to figure out is how to connect to MDM DB from iSeries, which is in another question I posed to the experts here.  Thanks again everyone!
Bob Loring
Minor added note... Kent's mention of sed brings up regular-expressions and Qshell utilities. Those are accessible from ILE RPG, so _potentially_ useful. However, native bifs like %scan() and %replace() will perform better in almost every case. They're good to keep in mind for particularly tricky cases though.

Tom
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Tom,

My apologies in forgetting about you earlier.  I hadn't seen you for a while and had tried to defer to Dave, forgetting that you too are quite the resource for AS/400 related items.

  :)


Kent
Kent:

No problem. Every once in a while, somebody has to work. I suspect that's where Dave is!

Tom

Author

Commented:
Thank you again Tom and Kent. I am just waiting for the EDW department to send me the JBDC Net drivers so I can connect from the iSeries to the Unix box.
Bob

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.