Solved

Generating SQL statements for DB2 objects

Posted on 2010-08-25
6
524 Views
Last Modified: 2012-05-10
Hi All,

I am using DB2 on AS400. I have a requirement to automate a database refresh process. In other words, we want to automate the process of using the production database to create test regions. I have a few questions on which I would appreciate some direction.

1) On the AS400 box, is there any way I can generate the SQL that was used to create that database object (say for instance, I have a function, i need to know what SQL was used to create that function)? I know this feature is available as "Generate SQL" on the iSeries Navigator. Is there anyway, i can do this on the back end AS400 box without using the iSeries GUI?
2) A lot of our functions, SP's and triggers have the production database name (say EXPPROD) harcoded. When copying over to the test database, is there any quick I can replace the value to the test database value (say EXPTEST)?

Any help on this regard will be appreciated.

Regards
Ali.
0
Comment
Question by:bhagatali
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 33524880

To my knowledge, the GUI is the only way to generate the SQL used to generate a database object.

As far as changing the heard-coded prodcution database name to a different value, the only way I can think of is a manual "search and replace" in your favorite text editor.

HTH,
DaveSlash
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33524909
Hi Dave,

UDB/LUW stores the source text when a view, function, or stored procedure is created.  I would expect the AS/400 version to do the same, but I don't know my way around its system tables very well.

I assume that the extracted object has all of the original comments and formatting?  If so, you can bet that DB2 is storing it somewhere.

But I sure don't know where that might be.


Kent
0
 

Author Comment

by:bhagatali
ID: 33524972
I agree with Kent. I had the same thought process. If we are able to see it on the UI, there has to be some place where that information (script) is stored. I am pretty sure that iSeries is not creating the script itself. I can be sure because, when i do a "Generate SQL", the comments that we have entered also show up.
0
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 
LVL 18

Accepted Solution

by:
Dave Ford earned 500 total points
ID: 33525017

Well, I do know that the source  for SQL procs is located in the ROUTINE_DEFINITION column in the QSYS2/sysprocs table.

It is a bit hard to read, though. I'd stick to the GUI.

HTH,
DaveSlash
0
 
LVL 27

Expert Comment

by:tliotta
ID: 33527585
Although SQL source is generally stored for SQL objects, there is a high probability that these aren't "SQL objects". There is a greater chance that these are native database objects that were created with native DDS rather than through any SQL statements.

Native database objects and SQL tables/views can essentially be used interchangeably by applications. SQL can access native files, and native I/O methods can access SQL tables/views.

So, if this system is older than just a very few years, there is probably very little SQL to retrieve. And in such a case, it would need to be "generated" which is obviously a very different action.

Tom
0
 

Author Closing Comment

by:bhagatali
ID: 33558083
Thanks for this input. I will see if there is any way i can read the data from this column and use it to generate an SQL.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question