We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


How do I check a file exists from AS400 SQL script?

Paul-Bailey asked
Medium Priority
Last Modified: 2013-12-06

I am researching how to convert our native iSeries database to an iSeries SQL database (apparently there are great performance benefits to be had). As I've worked with MS SQL Server before, I know that in a SQL script on SQL Server I can check if a file exists before I drop it by using IF EXISTS <file> THEN DROP <file>;.

Can anyone tell me how to do this "IF EXISTS" functionality on the iSeries? I've tried to COUNT(*) the records in the file, trying to return 0 if the file does not exist based on some suggestions in experts exchange for other types of SQL DB, but the "file does not exist" error pops up and ends the script. I do not want to resort to CL, RPG, or any other programming language - I want a script written purely in SQL, like I would do in MS SQL Server.

Sorry if this is already answered - I could not find a previous answer relevant to the iSeries.

Watch Question

Data Warehouse / Database Architect
Hi Paul,

Life is quite a bit different with DB2/LUW than with DB2 for the AS/400.  Most of it is for the better.

One big difference is data storage.  DB2/LUW doesn't map files as tables like you're accustomed to.  Tables are stored in larger containers called tablespaces.  A tablespace is a set of files that DB2 manages.  Deleting any of the files that DB2 expects will have catastrophic results!

If you want to check to see if a table exists, you can query the SYSIBM.TABLES view for the table name within a specific schema.

Good Luck,

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Of course!! Thanks very much Kent for that pointer.

Using this should help me out:
SELECT IFNULL(COUNT(*), 0) FROM sysibm/tables INTO tblCount WHERE          

But I can not use the above. I can not use DECLARE or SET within interactive SQL. I get the following:
SQL0104  30      11  Position 18 Token INTEGER was not valid. Valid tokens:
                     DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE.            
(where line 11 is the DECLARE statement above.)

While the DECLARE works within a PROCEDURE, then I am back to the beginning where I do not know if the procedure already exists before I create it in the script.

Perhaps I am going about this all wrong. How can I test that value from the COUNT on SYSIBM/Tables in a script?

- Paul.
Kent OlsenData Warehouse / Database Architect
Hi Paul,

As I said, "most of it" is for the better.  :)

The good news is that your query doesn't need IFNULL().  count() returns an integer so the result will never be NULL.

The bad news is that the kind of scripting that you're wanting to do can't be done from any client that I'm aware of.  Variables and program control (IF, WHILE, etc) are PROCEDURE items.

The good news is that most SQL can be run from a stored procedure.  It's pretty simple to pass the SQL to your own procedure and execute it.  One fairly common practice is to store the SQL statements in a table and have the procedure loop through the items in the table and execute them.

A more obtuse approach would be to run the SQL from the command line interface and pipe the output to a process that checks the return code or value.  It's fairly common to do in a unix environment, though it's far from "trivial".  It takes a pretty good comfort level with unix regular expressions.

in db2, you can't have a statement like
SELECT IFNULL(COUNT(*), 0) FROM sysibm/tables INTO tblCount WHERE          

in your code,
you need to either declare a cursor or use the into clause, in your case
INTO  :tblCount
FROM sysibm/tables INTO tblCount WHERE          

I'm not quite clear on the actual problem. That is, it revolves around a "script", but I'm not clear on what a "script" is in the context of AS/400 SQL. Is this a stored proc? Are these statements embedded in a program?

To me, the answer is to set a condition handler for the DROP TABLE statement to handle SQLSTATE-42704 ("An undefined object or constraint name was detected.")

Why worry about querying SYSTABLES at all?



Hi Tom,

When I say a script, I mean a semicolon-separated list of SQL statements in an iSeries source member that are run by the IBM command RUNSQLSTM (using the DFTRDBCOL parameter to specify where the file is to be (re-)created). See <http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/cl/runsqlstm.htm> for details of the RUNSQLSTM command.

In MS SQL Server, when you backup a table or table definition, a script file is created containing a sequence of drop and create statements that enable a table to be re-created in a schema where it already existed, or created in a brand new schema. I want to reproduce this kind of script exactly for an iSeries file.

Perhaps the term "script" is wrong and this may be confusing you. I must admit that one of the first things I had to learn when switching from SQL Server to iSeries databases is the complete change in terminology (e.g. SCHEMA = library, TABLE = file, COLUMN = field, etc.)

If I set a condition handler on each drop statement in the "script" then I will have to maintain more than I would if I have a section to detect if the file already exists, followed by a data backup and multiple drops if it does exist, followed by the create statements, then finally the data restore if the file existed.

I hope this answers your questions. Unfortunately for me, it is going to be a real pain implementing Kent's idea of a PROCEDURE running sequential statements from a seperate file - if anyone comes up with a much simpler way of doing what I want then I will be eternally grateful! :)

Kent OlsenData Warehouse / Database Architect

Hi Paul,

You can always feed the entire script through the command line interface.  If the SQL is formatted correctly, there should be no unwanted side-effects.  The DROP statements will fail if there's nothing to drop, but the following CREATE statement will work just fine as long as the object doesn't already exist.

By piping the output to a file, you can then use the unix tools to grab all of the SQL codes out of the output and ignore the ones that are non-issues, like DROP of an object that doesn't exist.  The only two subtleties that come to mind are:  1) depending on the database's RI, the DROP may fail if dropping the table would violate the RI;  and 2) you'll need to change the command terminator in your script ('@' works well).  Creation of function and stored procedures via the CLI requires that the command terminator used for the operation be different than the terminator used within the function or procedure.

A bit of unix scripting to extract the SQL errors is pretty easy.  Tying the error to a specific command via the unix script can be intimidating, but since the output is stored to a file it's pretty easy to review the file and find the error.


Ah! RUNSQLSTM source... yeah, 'script' is a reasonable term.

Unfortunately, a facility such as a condition-handler wouldn't fit. However, the RUNSQLSTM command itself might offer a possibility you can live with.

Note that RUNSQLSTM isn't commonly considered to be a "real" production-level way to perform production-level data manipulation by many System i developers. It's widely used, but usually for slightly less strict requirements.

In any case, the RUNSQLSTM mechanism would be through specifying a parameter like:

 ==>  runsqlstm ... errlvl( 20 )

A DROP TABLE statement will issue a severity level 20 error if the table doesn't exist. By telling RUNSQLSTM to accept ERRLVL(20), processing will continue with the next statement.

Note that this _might_ mean that you would break the script into two or more parts. Any part that might cause level 20 errors might need to be broken out if processing should stop at that point. Also, commitment control might need to be considered together with use of ERRLVL().


Kent's comment brought an additional possibility to mind. The 'command line interface' probably isn't quite what he's envisioning, but there are multiple interfaces that can be described as 'command line'. One of those is through the Qshell db2 utility. (Possibly there's also a PASE /AIX shell utility, but I've never needed to look.)

Qshell db2 accepts an SQL statement as an argument or can accept a filename with a set of statements.

Just a possibility.

Kent OlsenData Warehouse / Database Architect

Hi Tom,

I completely misread the original post.  :)  I thought that he was converting from iSeries to pSeries.  :/

Time for new glasses......

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.