Working Magic with IBM DB2 Universal Database (Windows)
This purpose of this article is to provide the Magic programmer first using IBM's DB2 database with a guide to
help him over some of the hurdles he may encounter. The information presented here is based on Magic 9.4
SP7q, DB2 gateway Version 9.4 SP7g-0 and IBM DB2 PE Universal Database Version 8.2. Much of it may be
useful in regard other versions.
1. Install DB2.
2. Install Magic eDeveloper.
During the installation make sure you include the DB2 gateway in the gateways to be installed. This installation program will copy "mgdb2.DLL" to the directory containing the gateways.
3. In the Magic.ini file [MAGIC_GATEWAYS] section make sure the following line is
not preceded with a semicolon at the beginning of the line:
MGDB18=C:\Program Files\Magic\9.40\Gateways\mgdb2.DLL
4. Create the Database in DB2.
5. Create the Database Schemas in DB2. As will be discussed later, DB2 schemas play a role when assigning an "Owner" to a table in eDeveloper. A DB2 schema is defined in IBM's DB2 Help as follows:
- A schema is a collection of named objects. Schemas provide a logical classification of objects in the database. A schema can contain tables, views, nicknames, triggers, functions, packages, and other objects.
- A schema is also an object in the database. It is explicitly created using the CREATE SCHEMA statement with the current user or a specified authorization ID recorded as the schema owner. It can also be implicitly created when another object is created, provided that the user has IMPLICIT_SCHEMA database authority.
- A schema name is used as the high order part of a two-part object name. As will be discussed later, DB2 schemas play a role when assigning an "Owner" to a table in eDeveloper. If the object is specifically qualified with a schema name when created, the object is assigned to that schema. If no schema name is specified when the object is created, the default schema name is used.
6. Set up the entry for the DB2 Database Management System in Magic eDeveloper.
Access Settings/DBMS. There should already be an entry for "DB2" on line 3. The following default values should be accepted under most circumstances.
Name: DB2
2 Phase: No
Nulls: No (This will not prevent making a particular column in a table "Null").
Excl. Trans.: No
Parameters: DB2 Parameters
Float: 10.3
ID: 19
7. Set up the corresponding entry for the DB2 Database in Magic eDeveloper.
a. Access Settings/Database.
b. Open a new line (F4) in the Databases table.
c. Type the Magic name you want to give to the database in the "Name" column.
d. Zoom into the "DBMS" column and select "DB2".
e. Type the name you gave the database in DB2 into the "Database Name" column.
f. Access the Database Properties (Ctrl+P) and go to the Options tab.
g. Check "Change Tables in Toolkit" if you want to create or modify tables in DB2 from Magic.
h. Check "Check Definition" so that discrepancies that may arise between eDeveloper and DB2 file structures are identified.
i. Select the value "None" in the "eDeveloper Locking" box.
j. Go to the SQL tab.
k. Check "Check Existence" while in development mode in order to create tables in DB2 from Magic. However, if other systems or software already access the tables or if the system is in production mode, it is advisable to leave "Check Existence" unchecked.
l. Click the "OK" button.
8. Set up the individual tables.
As with other databases, there are two ways to define tables:
Define the table in DB2 first, and then use the Options/Get Definition utility to import the definitions into the eDeveloper Table Repository.
- Use the DB2 first method if the database tables already exist, such as when they are already in use by existing systems.
- Caveat: When importing a column (field) defined in DB2 as LONGVARCHAR that has a length greater than 32637 (size of 32638), Magic will issue the error "Row length too big". This happens despite the fact that the Magic documentation for "DB2 Data Types" indicates that a picture of 32700 (storage size 32701) is permissible. This appears to be an error in Magic's eDeveloper DB2 gateway. When you import a column with length 32700, the size will import as "1" with a picture of zero resulting in the error "Wrong column size". MSE is aware of this since eDeveloper's documentation for "Row Length Is Too Big" acknowledges that 32767 is the limit.
- Note: The import will fill the Table Properties "Owner" field on the SQL tab with the tables DB2 schema.
Define the table in eDeveloper first, and then use the Generate Program option (Ctrl+G) to create the table definition in DB2.
- Note: Type the appropriate DB2 schema name in the Table Properties "Owner" field on the SQL tab. If you type a value in "Owner" that does not match an existing schema name in the DB2 database, generating or converting the table will create a new schema in DB2.
Page was generated in 0.0491 seconds
