USER_PATH indicates the user name or names (schemas) to be used when Database Connectors searches for files. The order of the names is significant. The syntax for this variable is:
USER_PATH user1 [user2]...
where the user argument may be either the name of a user (schema) on the system, or a period (“.”), which indicates the files owned by yourself.
The type of OPEN being issued for the file determines the effects of this setting.
Examples
If an OPEN INPUT or OPEN I/O is issued, and a USER_PATH variable is defined in the ACUFH configuration file, Database Connectors searches for a user of the named file in the list of users in USER_PATH. The first valid file is opened.
If USER_PATH is defined and the current user is the owner of the file, the current user must be included as one of the users, as indicated by a “.” or the setting of your login schema (A_DB2_LOGIN) in the USER_PATH. If this is not the case, even though the current user has created the table, it is not found and a file error 35 occurs. This circumstance can occur if the file is created with the OPEN OUTPUT phrase, and “.” is not an element in USER_PATH. When the table is created, the current user is the owner of that table. When the run time attempts to open the table, it does not look for tables owned by the current user unless USER_PATH is not set, or unless “.” or the user's current schema is part of the USER_PATH setting.
If an OPEN INPUT or OPEN I/O is issued, and no USER_PATH variable is in the ACUFH configuration file, Database Connectors searches for a user of the named file in the user named for login A_DB2_LOGIN). Database Connectors opens the first file that has a valid combination of user and file name.
If an OPEN OUTPUT is issued (whether USER_PATH is present or not), a new table is created with the owner being the name specified in A_DB2_LOGIN.
Using USER_PATH with other variables such as A_DB2_CATALOG can speed up the finding of tables in large databases. It can also prevent an error 14: More than one table with the same name, thereby enabling access to tables with identical names, but with different schemas.
For example, If using both USER_PATH and A_DB2_CATALOG , the form of the SQL statement changes from:
select COL1, ... from TABLENAME ...
to:
SELECT COL1, ... FROM [catalog.][username.]TABLENAME ...
where catalog and username are filled in if provided.