IBM i database frequently asked questions

What is Db2 for i?
Db2 for i is the relational database manager that is fully integrated into the IBM i product. Because it is integrated, Db2 for i is easy to use and manage. Db2 for i also provides many functions, such as triggers, stored procedures, and dynamic bitmapped indexing, that serve a wide variety of application types. These applications range from traditional host-based applications to client/server solutions to business intelligence applications.

As an interface to Db2 for i, the IBM DB2 Query Manager and SQL Development Kit for i licensed program adds an interactive query and report writing interface, as well as precompilers and tools, to help you write SQL application programs in high-level programming languages. Conforming to the industry standard SQL, the SQL implementation for the IBM i operating system allows you to define, manipulate, query, and control access to your data. It works equally well with IBM i files and SQL tables.

Back to questions
Is Db2 for i part of the DB2 family?
Yes. Db2 for i is a product of the DB2 family, which also includes DB2 for z/OS® and DB2 for Linux®, UNIX, and Windows.

Back to questions
How does Db2 for i relate to other DB2 products?
Each product of the DB2 family has its own unique code base, functions, and SQL syntax. However, there is technology sharing across the DB2 products. To learn about the relationships between the DB2 products, see the following information:
- Selected common SQL features for developers of portable DB2 applications contains information about the SQL features available across the DB2 products.
- Db2 for i Porting Information provides guides to help you move data between DB2 products. The porting guides also provide a brief history of the DB2 family.
Back to questions
What level of Db2 for i do I have?
If you are using a IBM i product, you have Db2 for i. The level of Db2 for i is based on the IBM i operating system and is independent of the DB2 versioning scheme. Because Db2 for i is included with the IBM i operating system, the version, release, and modification level of Db2 for i is the same as that of your operating system. This is typically expressed as VxRyMz, where x is the version, y is the release, and z is the modification. If you do not know the version, release, and modification of your operating system, follow these steps:
1. From System i® Navigator, right-click your system.
2. Select Properties.
3. If it is not already selected, click the General tab.
Back to questions
Does the system provide any sample databases?
Yes. You can find the sample tables and the system-provided stored procedure to create them in the Db2 for i sample tables of the SQL programming topic collection.

Back to questions
How do I install Db2 for i?
You do not need to install Db2 for i. It is included with the IBM i operating system release.

Back to questions
How do I authorize users to Db2 for i?
DB2 object access can be controlled by using the SQL GRANT and REVOKE statements, along with IBM i security interfaces. See Securing database files in the Database programming topic collection. System i Navigator can also be used to authorize users. For more information, see Authorizing a user or group using System i Navigator.

Back to questions
How do I administer Db2 for i?
You can administer Db2 for i using System i Navigator. Within System i Navigator, there is a tool for working with databases. You can work either from a traditional tree view of your database or from a visual representation of the database objects called Database Navigator. For details about using Database Navigator, see Mapping your database.

Back to questions

Database resources in the IBM i Information Center

Where are the DB2 manuals located?
You can find database manuals by following the PDFs link in the information center navigation tree under the Database category.

Back to questions
I'm having trouble printing a PDF. What should I do?
It is suggested that you save PDF files on your workstation for viewing and printing. To save a PDF, follow these steps:
1. Right-click the PDF link in your browser.
2. Click the option that saves the PDF locally.
3. Navigate to the directory in which you want to save the PDF.
4. Click Save.
Back to questions
What other resources exist to help me use the information center?
See Information center frequently asked questions. This FAQ contains tips for easily using the information center.

Back to questions

Data sharing and database migration

How do I move data between DB2 and other systems?
You can use the Copy From Import File (CPYFRMIMPF) and Copy To Import File (CPYTOIMPF) commands to import (load) or export (unload) data between systems. See Importing and exporting data between systems for instructions.

Back to questions
How do I migrate existing databases to Db2 for i?
IBM provides several guides to help you move data into Db2 for i from other databases, such as Oracle and SQL Server. For these guides, see Db2 for i Porting Information.

Back to questions
Can I store XML data in Db2 for i?
Yes. See the XML Extender Administration and Programming manual for tutorials on how to set up a database using provided sample data, how to map SQL data to an XML document, how to store XML documents in the database, and how to search and extract data from the XML documents.

Back to questions
Can I connect from my IBM i platform to a DB2 database that is on a platform other than IBM i?
Yes. You can learn more in User FAQs of the Distributed database programming topic collection.

Back to questions

Query for IBM i

How can I find all the queries that accessed a specific file?
You can follow these steps to produce a report of all the queries containing the file name:
1. Copy the code in Example: Finding the queries that accessed a specific file, replacing &LIBRARY and &FILE with your library and file names.
2. Create the command FFINQ using the Create Command (CRTCMD) command.
3. Specify GETQRYPRM as the program to process the command.
Back to questions
Does IBM provide any graphical query interfaces for the system?
Yes. The graphical query interface software provided for the system includes the DB2 Query Management Facility.

Back to questions

SQL-based queries

How can I join two members of the same table?
You can join two members of the same table by creating an alias for one of the members and joining them using the alias. To create an alias using SQL, see Creating and using ALIAS names or Creating database objects.

Back to questions
How can I run SQL statements from a text file?
You can run SQL statements from a text file using the Run SQL Statements (RUNSQLSTM) command. Or, you can use Run SQL Scripts from System i Navigator to run a text file on your PC or in the integrated file system. See Querying your database by running SQL scripts.

Back to questions
How can I write an SQL query that lists a set of detail records with a total at the bottom?
You can use a UNION operation to append the total as a row at the end of the list of items, as illustrated in the following example:
```
SELECT 'ITEM' AS ROWTYPE, PARTID, PRICE
    FROM PART
UNION
SELECT 'TOTAL' AS ROWTYPE, 0 AS PARTID, SUM( PRICE ) AS PRICE
    FROM PART 
ORDER BY ROWTYPE, PARTID
```
To make sure that the total row is at the end of the result set, you must include the Order By clause.

Back to questions
How can I select rows based on the current date in numeric or character fields?
The SQL CurDate function returns a date value, which cannot be directly compared to a number or an unformatted character value. You must do some conversion before the comparison. Use SQL functions to get the year, month, and day of the date as integers, and then create a numeric date in the form YYYYMMDD. See the code in Example: Selecting records based on the current date.

Back to questions
Can I create an alias on a system for a table, view, or physical file that is on another system?
No. When you create an alias, make sure that the table, view, or physical file for which you create it is on the current system. For more information, see CREATE ALIAS, with some syntax examples.

Back to questions
When the first member of a multimember database file has the same name as the file, it is the only member of the file that you can access using SQL. How can I access another member using SQL?
You can create an alias to point at the first member that you want to access.
Example 2: Create an alias named SALES_JANUARY on the JANUARY member of the SALES table. The sales table has 12 members (one for each month of the year).
```
CREATE ALIAS SALES_JANUARY
  FOR SALES(JANUARY)
```
For the complete example in the SQL reference topic collection, see CREATE ALIAS.
Back to questions

SQL messages

When are SQL messages displayed?
SQL messages are displayed when Db2 for i returns an error code to the application that uses it. The message text is displayed or logged at run time.

Back to questions
What does the SQL0901 message mean?
SQL0901: An SQL system error has occurred. This is the general message for all errors. For more information about SQL0901, see SQL messages and codes.

Back to questions
Where can I find a listing of SQL messages and codes?
You can find a complete list of SQL messages and codes in SQL messages and codes. Or use SQL message finder.

An application can also send the SQL message corresponding to any SQLCODE to the job log by specifying the message ID and the replacement text on the CL commands Retrieve Message (RTVMSG), Send Program Message (SNDPGMMSG), and Send User Message (SNDUSRMSG).

Back to questions

SQL packages

What are SQL packages?
SQL packages are permanent objects that are used to store information related to prepared SQL statements. They are used by Open Database Connectivity (ODBC) when the Extended Dynamic box is checked on a data source. They are also used by applications that use an API.

Back to questions
What are the advantages of using SQL packages?
Because SQL packages are a shared resource, when a statement is prepared, the information is available to all the users of the package. This saves processing time, especially in a situation when many users are using the same or similar statements. Because SQL packages are permanent, this information is also saved across job initiation and end, and is also saved across system restarting. In fact, SQL packages can be saved and restored on other systems. By comparison, dynamic SQL requires that each user go through the preparatory processing for a particular statement, and this must be done every time the user starts the application.

SQL packages also allow the system to accumulate statistical information about the SQL statements that result in better decisions about how long to keep cursors open internally and how to best process the data needed for the query. This information is shared across users and retained for future use. In the case of dynamic SQL, this information must be gathered by every job and every user.

Back to questions
What data is stored in an SQL package?
The SQL package contains all the necessary information to run the prepared statement. This includes registry of the statement name, the statement text, the internal parse tree for the statement, definitions of all the tables and fields involved in the statement, and the query access plan needed to access the tables at run time.

Back to questions
How can I tell what statements are in an SQL package?
Use the Print SQL Information (PRTSQLINF) command to produce a formatted report that shows the SQL statement and information about the access plan used to access the data.

Back to questions
How can I tell if the SQL package is being used?
Use the Print SQL Information (PRTSQLINF) command to produce a formatted report that shows the SQL statement and information about the access plan used to access the data. Use the database monitor to log information about SQL processing on the system. It includes the name of the package in the SQL summary records. The following statement shows the package, the SQL operation, and the statement text:
```
SELECT qqc103, qqc21, qq1000 from <db monitor file>
```
For ODBC, you can also look in the job log for the message Extended Dynamic has been disabled to determine if ODBC was unable to use an SQL package.

Back to questions

For more SQL package FAQs, see the DB2 Universal Database for iSeries Frequently Asked Questions: Improving Performance with SQL Packages Link outside information center Web site.

Stored procedures

How can I view the contents of a result set and the output parameters from a stored procedure call?
Use Run SQL Scripts to run the procedure in System i Navigator. First, open the Run SQL Scripts window. Then, call the stored procedure using the CALL statement, pass the parameters to the statement, and run the procedure. The parameters are returned to the Result Set tab, and the output parameters are returned to the Messages tab.

Back to questions
Can existing RPG or COBOL programs be used as stored procedures?
Yes. Db2 for i supports external stored procedures, which allows existing high-level programs to be called as stored procedures. The CREATE PROCEDURE statement is used to register these programs as stored procedures.

Back to questions
I create a procedure that contains SQL statements that reference user-defined functions. Before calling the procedure, I run a SET PATH statement so the functions are found. Why aren't the functions found when I call the procedure?
The SET PATH statement must be run before the CREATE PROCEDURE statement. The path for static statements in a precompiled program is determined when the program is created. In the case of CREATE PROCEDURE, an SQL C program is created. Dynamic statements in the procedure use the current path, but the static statements in the procedure use the path that was used at the time of creation. This is also true for the CREATE FUNCTION statement.

Back to questions
How do I find out if a stored procedure exists, in what library it exists, and what the attributes of the stored procedure are?
To see the stored procedures on the system, you need to query the SYSPROCS catalog view. For example:
```
SELECT * from sysprocs where routine_name = 'MYPROC' and routine_schema = 'MYLIB'
```
This checks to see if there is a stored procedure named myproc in mylib. If you do not know the library, do not include the routine_schema part of the search condition in the query. See IBM i catalog tables and views for the definition of all the columns for the SYSPROCS catalog. You might also be interested in the SYSPARMS catalog, which contains the parameter definitions for a procedure.
Back to questions

Triggers

What is a trigger?
A trigger is a set of actions that are run automatically when a specified change or read operation is performed on a specified table or on a specified physical database file. For more information, see Triggering automatic events in your database.

Beginning in V5R1, you can also use SQL triggers. The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables whenever an insert, an update, or a delete operation is performed. The statements specified in the SQL trigger are run each time an SQL insert, update, or delete operation is performed. An SQL trigger can call stored procedures or user-defined functions to perform additional processing when the trigger is run. For more information, see SQL triggers.

Back to questions
Why does my trigger program not work after upgrading the IBM i operating system?
The offset of your record might have changed. For several releases in the past, the offset did not change. It did, however, change for V5R1 and might change in subsequent releases. The easiest solution is to always code your trigger programs to use the offsets and lengths passed in the trigger buffer. For a summary of fields in the trigger buffer, see Trigger buffer field descriptions.

Back to questions
Can an SQL trigger access the trigger buffer?
The body of an SQL trigger can use the REFERENCING NEW ROW and REFERENCING OLD ROW clauses to reference the fields in the before and after record images that are stored in the trigger buffer. However, an SQL trigger program cannot reference the trigger buffer directly, nor can it reference the other information in the buffer. For example, the trigger time and trigger event are not available to an SQL trigger.

Back to questions