Specifies a user-defined initialization command to execute immediately after every connection to the DBMS that is within the scope of the LIBNAME statement or libref.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Aliases: | DBINITCMD [Vertica] |
| INITCMD [Vertica] | |
| Default: | none |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Spark, Teradata, Vertica, Yellowbrick |
| Notes: | Support for Spark was added in SAS 9.4M7. |
| Support for Yellowbrick was added in SAS 9.4M7. | |
| See: | AUTHID= LIBNAME option, AUTHID= data set option, DBCONTERM= LIBNAME option, SCHEMA= LIBNAME option, SCHEMA= data set option |
Table of Contents
any valid command that the SAS/ACCESS engine can execute and that does not return a result set or output parameters.
The initialization command that you select can be a stored procedure or any DBMS SQL statement that might provide additional control over the interaction between your SAS/ACCESS interface and the DBMS.
The command executes immediately after each DBMS connection is successfully established. If the command fails, a disconnection occurs and the libref is not assigned. You must specify the command as a single quoted string.
DB2
under z/OS: If you specify DBCONINIT="SET
CURRENT SQLID='user-ID'",
then any values that are specified for AUTHID= or SCHEMA= are ignored.
Only the value that you specify for SQLID= is used in SQL or procedures
that are passed to the database.
In this example, the DBCONINIT= option causes the DBMS to apply the SET statement to every connection that uses the MYDBLIB libref.
libname mydblib db2
dbconinit="SET CURRENT SQLID='myauthid'";
proc sql;
select * from mydblib.customers;
insert into mydblib.customers
values('33129804', 'VA', '22809', 'USA',
'540/545-1400', 'BENNETT SUPPLIES', 'M. JONES',
'2199 LAUREL ST', 'ELKTON', '22APR97'd);
update mydblib.invoices
set amtbill = amtbill*1.10
where country = 'USA';
quit;In this example, a stored procedure is passed to DBCONINIT=.
libname mydblib oracle user=myusr1 pass=mypwd1
dbconinit="begin dept_test(1001,25)";
end;The SAS/ACCESS engine retrieves the stored procedure and executes it.
In this example, specify that a backslash character (‘\’) should be read as a literal character rather than as an escape character. By default, the DBMS variable that controls how the backslash is read is disabled, resulting in a backslash being treated as an escape character. If this is not the desired behavior (such as when specifying a directory path), you can change the behavior.
The command that you specify varies based on your DBMS. For Greenplum, specify the following command for DBCONINIT=:
dbconinit="SET standard_conforming_strings = 'ON'"For Aster, specify this command for DBCONINIT=:
dbconinit="set session enable_backslash_escapes='off'"For MySQL, specify this command for DBCONINIT=:
dbconinit="set session sql_mode='no_backslash_escapes'"