Chapter 4: Getting Ready to Install

Create the Sybase collection on the AS/400 (DC for DB2 UDB)

The access service requires certain files on the AS/400. DirectConnect for DB2 UDB access service looks for these files in a collection called “SYBASE.” You must create this collection and name it “SYBASE” before installing the access service.

The owner of the SYBASE collection must have QSECOFR privileges.

The SYBASE collection contains a package for the DC DB2 UDB driver that is created when the drivers are installed. Once you create the packages, you need to grant permissions to PUBLIC for the end users to access the Sybase collection located on the target AS/400. This can be done with isql (which comes with the DirectConnect installation), or directly on the AS/400 machine.

To grant permissions from isql

Issue the following command:
```
GRANT EXECUTE ON PACKAGE SYBASE.DEF000x TO PUBLIC
```
The DirectConnect service used to issue this command must be in sybase mode for SQL transformation (SQL transformation is an access service property with one of two settings, passthrough or sybase). To ensure this, enter the following command in the isql session before you issue the GRANT EXECUTE command:
```
set sqltransformation sybase
```

Because the AS/400 allows you to change security levels and ownership of objects, you can use another user ID to create the SYBASE collection. For example, you can use one ID to create the SYBASE collection, then grant other users create and execute authorization for the collection and its objects.You can create the SYBASE collection in one of two ways:

If you have DB2 (or DB2/400) Query Manager and SQL Development Kit (SDK) installed on the AS/400, you can run the SQL utility using a 5250 session.
If not, you need to use the AS/400 SEU utility program to create the SYBASE collection.

To grant permissions directly on the AS/400

Log on to the AS/400 at the main console, a 5250 terminal, or a 5250 terminal emulator, using the user ID that will be the owner of the SYBASE collection.
To display the Sybase packages, on the command line, enter:
```
WRKOBJOWN SYBASE
```
On the Work with Object by Owner window, locate and select a package from the attribute column and in the corresponding OPT column, enter the following:
```
2
```
On the Edit Object Authority window, in the User column, locate *PUBLIC, and in the corresponding Object Authority column, enter:
```
ALL
```
Press F12 to return to the Work with Object by Owner window, and repeat steps 2 and 3 until all packages are completed. To exit, press F3 repeatedly, followed by 90.

Creating the SYBASE collection through the AS/400 SEU utility

The user ID for creating the SYBASE collection must have a valid CCSID for your language installation. For more information, see "Change the CCSID" section.

To create the SYBASE collection through the AS/400 SEU utility

Log on to the AS/400 at the main console, a 5250 terminal, or a 5250 terminal emulator, using the user ID that is to be the owner of the SYBASE collection.
Start the SEU program by entering the following command:
```
STRSEU
```
Press F4 to display the Work With Members Using SEU window.
Provide the following information:
- In the Source File field, enter:
```
QQMQRYSRC
```
- In the Source Member field, enter:
```
*SELECT
```
- In the Library File field, enter:
```
QGPL
```
  If the source file does not exist, you must create it using the CRTSRCPF command.
Press Enter to list the members of the source file.
Build the query that creates the collection by creating a source member for the query:
- In the New Member field, enter:
```
SYBASE
```
- In the Type for New Member field, enter:
```
TXT
```
Press Enter to display the Edit window.
Create the query member by entering the following in the first line of the source file:
```
CREATE COLLECTION SYBASE
```
Press F3, and verify that the Change/Create Member field is set to Y, and the Member field is SYBASE. Press Enter to exit the editor.Now you can work with the query you created.
Press F3 to exit and return to the Main menu.

To build the query that creates the SYBASE collection

Enter the following on the command line:

CRTQMQRY QMQRY(QGPL/SYBASE) SRCFILE(QGPL/QQMQRYSRC)

Press Enter. The AS/400 utility creates the query.You might receive a warning message that the source length exceeds 79 characters. Disregard this message, as it does not affect the access service.
To run the query that creates the SYBASE collection, enter the following on the command line:
```
STRQMQRY QMQRY(QGPL/SYBASE)
```
Press Enter. The SYBASE collection is created.

Building the collection might take a few minutes. During this time, you will receive messages indicating the status of the operation.

Creating the SYBASE collection through DB2 or DB2/AS400 Query Manager and SDK

The user ID for creating the SYBASE collection must have a valid CCSID for your language installation. For more information, see "Change the CCSID" section in the Installation Guide.

To create the SYBASE collection through SQL/400

Log on to the AS/400 at the main console, a 5250 terminal, or a 5250 terminal emulator, using the ID that you want to own the collection (usually SYBASE).
On the command line, enter:
```
STRSQL
```
On the SQL command line, enter:
```
CREATE COLLECTION SYBASE
```
The collection process requires a few minutes to complete. You can create other collections by using this same syntax and substituting the name of the collection for "SYBASE."

Authorizing users for the SYBASE collection

You must have the following minimum authorizations to access the AS/400 through the access service:

USE authorization to the service package in the SYBASE collection
CHANGE authorization to:
- Journal object QSQJRN
- Journal receivers QSQJRN0001, QSQJRN002, and any other additional journal receivers

To confirm that authorizations are set properly

Verify that the *SQLPKG objects in SYBASE are set to *PUBLIC, *USE, or higher authorization level.
Verify that the journal objects are set to *PUBLIC, *CHANGE, or higher authorization level.

View this book as PDF