Solved

Generating SQL statements for DB2 objects

Posted on 2010-08-25
6
520 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
db2 z/os V10 access path 7 360
Problem to script 14 208
Catalog Index 9 131
Importing Android SQlite database data to db2 4 100
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

752 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