SQL Pass-Through Facility Specifics for Vertica

Key Information

For general information about this feature, see SQL Pass-Through Facility.

Here are the SQL pass-through facility specifics for the Vertica interface.

• The dbms-name is VERTICA.
• The CONNECT statement is required.
• PROC SQL supports multiple connections to Vertica. If you use multiple simultaneous connections, you must use the alias argument to identify the different connections. If you do not specify an alias, the default VERTICA alias is used.
• The database-connection-arguments for the CONNECT statement are identical to its LIBNAME connection options.

CONNECT Statement Examples

This example uses the DBCON alias to connect to the mydb1 Vertica database, execute several queries, and then disconnect. The connection alias is optional.

proc sql;
   connect to vertica as dbcon
   (server=mysrv1 port=5433 user=myusr1 password=mypwd1 db=mydb1);

execute (drop table scnpastbl) by dbcon;
execute (create table scnpastbl (id int, 
   name char(13), tel char(11), constraint_vpk 
   primary key(id, tel))) by dbcon;
execute (insert into scnpastbl values 
   (1, '111', '1-1-1')) by dbcon;

select * from connection to dbcon 
   (select * from scnpastbl);

   disconnect from dbcon;
quit;

In this example, the PRESERVE_COMMENTS argument is specified after the USER= and PASSWORD= arguments. The Vertica DBMS SQL query is enclosed in the required parentheses.

Connect to vertica DBMS as mycon(user=myusr1
password=mypwd1 preserve_comments);
select *
from connection to mycon
(select /* +indx(empid) all_rows */
count(*) from employees);
quit;

Hints are not preserved in this next example, which uses an older style of syntax.

execute ( delete /*+ FIRST_ROWS */ from test2 where num2=1) by &db

Using the new syntax, hints are preserved in this example.

execute by &db ( delete /*+ FIRST_ROWS */ from test2 where num2=2);