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
Solved

Generating SQL statements for DB2 objects

Posted on 2010-08-25
6
512 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
6 Comments
 
LVL 18

Expert Comment

by:daveslash
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 18

Accepted Solution

by:
daveslash 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 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