Solved

Generating SQL statements for DB2 objects

Posted on 2010-08-25
6
497 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:Kdo
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

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…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now