|
Database
What databases does OS/400 support?
OS/400 supports DB2 Universal Database (UDB) for iSeries. The database is integrated into the operating system and is shipped with every iSeries system. For more information see What Does DB2 UDB on the iSeries Really Mean?
How can I port database design and tables to DB2 UDB for iSeries?
The SQL scripts can be used in OS/400 with minimal changes to create the database elements (i.e. tables, indexes, etc.) However, there are some tools available which can be used to port the database design and the data to DB2 UDB for iSeries from other RDBMSs like Oracle, Sybase etc. For example, data modelling tools can be used to reverse engineer SQL. Examples are Platinum's ERwin
How (and how well) does DB2 UDB for iSeries interoperate with other databases?
To connect to the DB2 UDB for iSeries database from another system, OLE DB connections from a PC client.
To connect to other systems' databases from an iSeries system you can use DRDA protocols, a pure Java JDBC driver, or IBM's DataJoiner.
What are the data types supported by DB2 UDB for iSeries?
DB2 UDB for iSeries supports following data types:
- Fixed length character string
- Varying length character string
- Fixed length graphic character string
- Varying length graphic character string
- Date
- Time
- Timestamp
- Small Integer
- Large Integer
- Packed decimal
- Zoned decimal
- Single floating point
- Double floating point
In V4R4 and later:
- BLOB (binary large object)
- CLOB (character large object)
- DBCLOB (double-byte character large object)
- Datalink
- UDT (user-defined data types in the form of distinct data types)
What locking level does DB2 UDB for iSeries use, row level or page level?
DB2 UDB for iSeries uses true row level locking.
What is the difference between a physical file created by DDS and a TABLE created using SQL?
A physical file created using DDS is the same as creating a table using SQL. In fact DDS and SQL are just two interfaces to DB2 UDB for iSeries.
What are journal and journal receivers?
Journal and journal receivers are used to log all the database activities and changes made to the data in the database. It is used for recovery purpose after a failure has occurred. It is also used if some changes are to be rollbacked.
Does DB2 UDB for iSeries support stored procedures? Can one port stored procedures from other RDBMS like Oracle or Sybase to DB2 UDB for iSeries?
Yes. DB2 UDB for iSeries supports stored procedures. On iSeries stored procedures can be written in in any high level language (RPG, COBOL, C, etc.) that may or may not have embedded SQL. DB2/400, from V4R2 forward, supports SQL procedures like Oracle PL-SQL and Sybase/SQL Server Transact-SQL. Initially, many other databases created proprietary SQL syntax for stored procedures which may make them difficult to port to iSeries. The DB2/400 SQL procedure support is fully ANSI-compliant. For developing SQL procedures on DB2/400 releases before V4R2, Porting Stored Procedures to DB/2 for OS/400 highlights some porting issues involved.
NOTE: The runtime support for SQL procedures is in OS/400. The SQL Development Kit and the ILE C products are required only for the developer, not the customer. Both of these licensed program products are required to create SQL procedures.
Does DB2 UDB for iSeries support distributed two phase commit?
Yes.
What is the DRDA level supported on DB2 UDB for iSeries?
DB2 UDB for iSeries supports DRDA level 2, (i.e. Distributed Unit of work (DUW)) over SNA connections. It also supports Remote unit of work (RUW).
What is scope of a connection?
Connection is scoped to an activation group.
Can I connect to more than one database? How do I switch between database connections?
DB2 UDB for iSeries does not support the notion of independent, isolated databases on iSeries systems - instead DB2 UDB for iSeries is implemented as a single system-wide database.
Collections (ie, schemas) are the logical containers for related database objects in OS/400. Any database object within a Collection can be accessed by any user as long as they have the proper authorization (without a CONNECT statement). In OS/400's object-based terminology, an SQL 'collection' is a LIBRARY.
If a connection is needed to another DB2 server or any DRDA compliant server, the SQL CONNECT statement is used. The SET CONNECTION statement is used to switch between connections to multiple servers. With DB2 UDB for iSeries, this database name it typically just the system name, once connected the client can access any database object to which it has been given authority.
What is scope of a COMMIT?
The scope of commitment control can be set by the user. It can be specified to be at JOB level or at Activation Group level. STRCMTCTL CL command can be used to start and scope the commitment control.
Does OS/400 have callable SQL APIs which can be used to make an application portable?
Yes. For C++, OS/400 supports SQL Call Level Interface (SQL/CLI) which can be used for database access. This set of APIs is compliant with The Open Group's definition, which is also what was used as a basis for Microsoft's ODBC specification. For Java, native JDBC driver APIs (see JDBC FAQ ) or SQLJ can be used.
What standards does DB2 UDB for iSeries comply with?
The SQL entry-level standard (1992).
Does DB2 UDB for iSeries support Unicode?
Yes. Use a field type of GRAPHIC with a CCSID of 13488. For more information, see the chapter on UCS-2 in iSeries International Application Development.
Does DB2 UDB for iSeries support triggers?
Yes. For complete information about triggers, see DB2 UDB for iSeries Trigger Support or DB2 UDB for iSeries Database Programming in the Information Center.
Where can I find education about DB2 UDB for iSeries?
See the "Education Resources" link from DB2 main page.
See also: Porting to DB2 UDB for iSeries
What is CLI?
DB2 Call Level Interface (CLI) is a callable Structured Query Language (SQL) programming interface simialr to ODBC. It is supported in all DB2 environments except for DB2 for MVS and DB2 for VSE and VM. A callable SQL interface is an application program interface (API) for database access, that uses function calls to start dynamic SQL statements. It is an alternative to embedded dynamic SQL. The important difference between embedded dynamic SQL and DB2 CLI lies in how the SQL statements are started. On the iSeries system, this interface is available to any of the ILE languages and in OS/400 PASE.
Here are some helpful references about OS/400's SQL Call Level Interface (CLI):
How does CLI on DB2 UDB differ on iSeries systems?
Following is a list of the differences you may encounter with the SQL CLI for DB2 UDB for iSeries. The DB2 versions for "NT" and AIX include a CLI layer based on the Microsoft ODBC specification. The differences listed here are present because the DB2 UDB for iSeries CLI implementation is based on the approved, open CLI standard from X/Open.
Please note: This list is not intended to be an exhaustive list of every technical detail of the differences you will encounter on DB UDB for iSeries. There are other subtle differences (such as differences in the constant value of SQL_VARGRAPHIC).
- DATE, TIME and TIMESTAMP values are always passed as fixed-length strings in OS/400. MS-ODBC uses structures for these types.
- VARCHAR type columns are different in that they're a structure of a 2-byte integer length field plus the data.
- On most 'open' platforms, decimal types are approximated with floating point numbers, because the hardware they run on generally does not support exact numeric representation. The iSeries system has hardware support for packed decimal (exact-number) operations, and this is used for DECIMAL types in SQL. This means that a C program must use the decimal types, not the floating point C types for DECIMAL columns. (float & double C types are available for FLOAT and DOUBLE columns, as on other platforms).
- There are some functions (for example SQLSetStmtAttr) in the MS-ODBC APIs that expect either a pointer or an integer for the value of some parameter. They can get away with this on some operating systems because pointers and long integers are both only 32 bits, and are equivalent data types. The OS/400 compilers, however, don't allow this because OS/400 pointers are 128 bits and protected, while ANSI long integers are 32 bits. This will be a very common problem that affects other (non-iSeries) operating systems as they try to move to even 64-bit addressing.
This table provides more details about CLI differences for DB2 UDB for iSeries.
[BACK]
|
