Solved

Generating SQL statements for DB2 objects

Posted on 2010-08-25
6
518 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: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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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: 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

Suggested Solutions

Title # Comments Views Activity
DB2 tablespace disk full error 33 1,203
SQL subselect has mulitple rows incoming as opposed to a single row 3 503
I need a like query for db2 3 90
DB2 return first match 3 122
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 (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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 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