| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Access Service User's Guide DirectConnect |
![[-]](/dwicons/i_colpse.gif)
| Chapter 13 Retrieving information with System Procedures |
Chapter 13
System procedures are Sybase-supplied stored procedures that return information about the access service and the target database. This chapter describes the system procedures that an access service supports:
If an access service cannot support a procedure, that procedure returns a correctly formatted result set containing zero rows.
A client application initiates a system procedure in the same way that it initiates a CSP. For instructions to code both CSP and system procedures, see "Coding Instructions" .
Returns the SQL capabilities of an access service.
sp_capabilities
The result set contains information that allows applications to successfully interact with an access service during normal query processing.
ResultsThe following table shows the result set:
Column | Datatype | Description |
ID | int | Capability ID |
CAPABILITY_NAME | char(30) | Capability name |
VALUE | int | Capability value |
DESCRIPTION | char(128) | Capability description |
The following table shows the ID and values for several access service functional capabilities:
ID | Capability | Value Description |
101 | SQL syntax | 1=Sybase T-SQL supported 2=DB2 SQL supported |
102 | Join handling | 0=Unsupported 1=No outer join supported 2=T-SQL support 3=Oracle supported |
103 | Aggregate handling | 0=Unsupported 1=ANSI supported 2=All functions |
104 | AND predicates | 0=Unsupported 1=Supported |
105 | OR predicates | 0=Unsupported 1=Supported |
106 | LIKE predicates | 0=Unsupported 1=ANSI-style supported 2=T-SQL supported |
107 | Bulk insert handling | 0=Unsupported 1=Supported |
108 | Text and image handling | 0=Unsupported 1=Text, no textptr 2=Text and textptr |
109 | Transaction handling | 0=Unsupported 1=Local supported 2=Two-phase commit supported |
110 | Text pattern handling | 0=Unsupported 1=Pattern (text) supported |
111 | order by | 0=Unsupported 1=Supported |
112 | group by | 0=Unsupported 1=ANSI supported 2=T-SQL supported |
113 | Net password encryption | 0=Unsupported 1=Supported |
114 | Object case sensitivity | 0=Case insensitive 1=Case sensitive |
115 | distinct | 0=Unsupported 1=Supported |
116 | Wild card escape | 0=Unsupported Non-zero=Escape_char(s) |
117 | Union handling | 0=Unsupported 1=Supported |
118 | String functions | 0=Unsupported 1=Substring supported 2=Oracle subset supported 3=T-SQL supported |
119 | Expression handling | 0=Unsupported 1=ANSI supported 2=T-SQL supported |
120 | Character truncation | 0=Fixed length character parameters may contain trailing blanks 1=Fixed length character parameters will not contain trailing blanks |
121 | Language events | 0=Unsupported 1=T-SQL DML without datetime in the where clause supported 2=T-SQL DML supported |
122 | Date functions | 0=Unsupported 1=T-SQL date functions supported |
123 | Math functions | 0=Unsupported 1=Oracle functions supported 2=T-SQL math functions supported |
124 | T-SQL convert functions | 0=Unsupported 1=Supported |
125 | T-SQL delete/update | 0=Sybase extensions not supported 1=Sybase extensions supported |
126 | Insert/select handling | 0=Unsupported 1=Supported |
127 | Subquery handling | 0=Unsupported 1=Supported |
128 | IN/NOT IN support | 0=Unsupported 1=Supported |
129 | CASE support | 0=Unsupported 1=Supported |
Returns a list of groups to which the current user belongs.
sp_groups
The following table shows the result set:
Column | Datatype | Description |
GROUP_NAME | char(8) | Group Name (Authorization ID) |
Provides the user with a report containing the following rows of information:
Row 1: the version of Open Server currently in use
Row 2: the version of DirectConnect Server currently in use
Row 3: the version of DirectConnect Access Service Library currently in use
Row 4: the version of the DBMS with which an access service is associated
sp_helpserver
The following table shows the sp_helpserver result set.
Column Name | Datatype | Description |
SERVER_PROPERTY | varchar(32) | Server property name |
PROPERTY_VALUE | varchar(32) | Server property value |
Changes or queries the current status of the user's password.
To change the user's password, use the following syntax:
sp_password old_password, new_password, [login_name]
To query the current status of the user's password, use the following syntax: sp_password
is the current password for the user of this procedure or login name.
new_passwordis the new password for the user. The new password must conform to the rules required on the DBMS in which the password change is to take effect.
login_nameis the login name of the user whose password is to be changed.
The mainframe security system rejects the request if the user lacks the authority to change another user's password.
You can program your client applications to use sp_password to avoid password expiration problems. Program an application to issue sp_password upon application start-up, and prompt the user for a new password if the existing password is close to expiration. Also, you can build a simple application that reads user IDs and passwords from a file and executes sp_password to make the appropriate modifications.
For DB2, the access service invokes the CICS transaction called Password Expiration Manager (PEM). PEM is a password management program that IBM provides with CICS 3.3 through an optional PTF UN90057. PEM is available only for connections to the mainframe using LU6.2. It is not available for TCP/IP connections.
To change the setting of the CICS SIT table property, ask the CICS system programmer and the external security manager. The SIT property defines the intersystem refresh delay, which determines how long users remain signed on to the host when running transactions with the InterSystem Communication (ISC) setting. Its setting can affect the ability of users to log in more than once or to run multiple host transactions from TRS within the defined time period. By default, the delay is set to 30 minutes. We recommend setting ISRDELAY=0.
ResultsThe following table shows the result set:
Column | Datatype | Possible Return Values and Descriptions |
RETURN_CODE | smallint | 0 = The PEM operation returned no errors. 1 = The user ID was not found on the host. 2 = The password is incorrect. 3 = No new password was specified and the old password expired. 4 = The host security system rejected the new password. 5 = A security function failure occurred. Your password account may be revoked. 6 = An invalid request was made to PEM. The new or old password may be in an unacceptable format. 7 = General security error. 8 = Password change completed but signon failed. |
CURRENT_DATE | datetime | Date and time of current successful signon. |
LAST_DATE | datetime | Date and time of last successful signon. |
EXPIRE_DATE | datetime | Date and time password will expire. |
REVOKE_COUNT | smallint | Number of unsuccessful signon attempts since the last successful signon with this user ID. |
Provides information about SQL parameters that the target database supports for the specified attribute.
sp_sqlgetinfo [attribute_name]
is the name of the attribute about which information is requested. Wildcard characters are supported.
If the attribute is not found in the internal table, the access service returns an empty result set.
If a parameter is not provided, the access service returns a result set of all supported attributes.
This stored procedure provides SQL grammar, syntax, and capabilities that are supported on the target database.
The access service stores this information in a file that you can modify if necessary. The SQLInformationFile property specifies the path and file name. For more information, see "SQLInformationFile" .
The following table shows the sp_sqlgetinfo result set.
Column Name | Datatype | Description |
ATTRIBUTE_ID | intnot NULL | The numeric identifier for the attribute |
ATTRIBUTE_NAME | varchar(30) not NULL | The name of the attribute |
ATTRIBUTE_VALUE | varchar(255) NULL | The value of the attribute |
|
|