Using the PSQL Tool for Bulk Loading -How to sas analysis to psql

[shursulei]

Now ,i have use the sas_94 M6 and suse12 ,and postgresql is 9.2.24 .When we use access to postgresql write data to postgresql with Bulkload.The way is used the PSQL Tool for Bulk Loading.

The code.

libname x2 postgres server="xxxx" port=xxx database="xx" xxxxx;

proc sql;

create table x2.class_test(bulkload=YES bl_psql_path='/opt/client/psql/psql' bl_delete_datafile=no)

as select  * from sashelp.cars;

quit;

/*The error log

psql_exec :waring: extra command-line argument "dbname=xxxx" ignored

psql_exec :waring: extra command-line argument "host=xxxx" ignored

psql_exec :waring: extra command-line argument "port=xxx" ignored

psql_exec :waring: extra command-line argument "password=xxxx" ignored*/

Question: sas how to pass parameter to psql。such as dbname host etc

[AnandVyas]

Hi @shursulei 

Usually the db host name and other details are provided in the libname statement itself while making a library connection. Bulk loading only supports few options as doucmented in the link here 

[shursulei]

How the sas analysis the connections information

[AnandVyas]

If you are asking on how the libname works then for most of the DBs, SAS uses the information passed via various parameters in the libname statement to the DB client to make a connection.

[shursulei]

I have set
option sastrace='d,,d,d'
to result the log.
The log appear the below .

[BULKLOAD] Psql command :export PGPASSWORD=xxxx ;"/opt/mppdb_client/psql/psql" --no-psqlrc -v VERBOSITY=verbose -v ON_ERROR_STOP=1  -d "sslmode=prefer user=mppcra dbname=icbc_edw_dev host=xxx port=25308 password=xxxx " -f "/tmp/xx/xxx.ctl" 2> "/tmp/xx/xxx.log"

now i want to how no to such as 

[BULKLOAD] Psql command :export PGPASSWORD=xxxx ;"/opt/mppdb_client/psql/psql" --no-psqlrc -v VERBOSITY=verbose -v ON_ERROR_STOP=1  --username=mppcra --dbname=icbc_edw_dev --host=xxx --port=25308 --password=xxxx " -f "/tmp/xx/xxx.ctl" 2> "/tmp/xx/xxx.log"

why not as the below.The log's paramter is not used

[AnandVyas]

I don't get what you are asking here. However seeing the outputs you have shared I can see that there is a slight difference on how the connection string is specified. First one uses -d "..." to specify all the options and second one explicitly mentions each parameter starting with --. I don't think that matter as long as the connection string works.

[shursulei]

The code is the same.
The first is sas94M6's result.The bulkload is error.
The second is sas94M1's result.The bulkload is OK.
so i want to now how to the sas pass parameter to the psql

[AnandVyas]

I ran the same code (sample shared in original post) in 9.4M6 and it works fine for me. The log you have shared in your original post only has warnings. Do you have any logs with error?

[shursulei]

The code and log is attachments

[AnandVyas]

Hi @shursulei 

The log you have shared has this following error:

ERROR: invalid value for parameter "client_encoding": "GBK"Conversion between GBK and LATIN1 is not supported.

I guess there is a difference in the client encoding for your 9.4M1 rel and 9.4M6 rel. There is a parameter 'client_encoding' in the postgresql.conf file. You may have to verify that. Incase you wish to over-ride for particular SAS Session you can use environment variable 'export PGCLIENTENCODING=<encoding>' on system level.

[shursulei]

The postgresql client is LATIN1.

I cann't understand the log occur as below:

************** Begin:  PSQL Log File **************
/opt/mppdb_client/psql/psql: line 7: /tmp/exec_log2: Permission denied
psql_exec: warning: extra command-line argument "dbname=xxxx" ignored
psql_exec: warning: extra command-line argument "host=xxxx" ignored
psql_exec: warning: extra command-line argument "port=xxxx" ignored
psql_exec: warning: extra command-line argument "password=mppcra@123" ignored
psql_exec: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/opt/huawei/Bigdata/mppdb/mppdb_tmp/.s.PGSQL.5432"?

************** End:    PSQL Log File **************

[AnandVyas]

What abt your SAS Session encoding? Is it GBK? Can you check your session encoding to LATIN1 and run the code?

[shursulei]

The code is below .

libname x2 postgres server="xxxxxxxxx" port=xxx database="xxx" schema="public" user=mppcra  password="mppcra@123" client_encoding="lantin1" ;
option sastrace='d,,d,d' sastraceloc=saslog nostsuffix;
proc sql;
create table x2.class_clas_testxxxx(bulkload=YES bl_psql_path='/opt/mppdb_client/psql/psql' bl_delete_datafile=no)
as 
select * from sashelp.cars;
quit;

The before's encoding problem don't have.

The error

************** Begin:  PSQL Log File **************
psql_exec: warning: extra command-line argument "dbname=xxxx" ignored
psql_exec: warning: extra command-line argument "host=xxxxx" ignored
psql_exec: warning: extra command-line argument "port=xxxx" ignored
psql_exec: warning: extra command-line argument "password=mppcra@123" ignored
psql_exec: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/opt/huawei/Bigdata/mppdb/mppdb_tmp/.s.PGSQL.5432"?

************** End:    PSQL Log File **************

[AnandVyas]

Suggest that you mask sensitive information before posting them on public forum.

Error states that you cannot connect to server. Could you check if PostgreSQL server is running? you can try below command to check connections.

netstat -an | grep 5432

Also, If possbile just run the libname to verify if the library connection is successful or not. Please share full logs, If possible.

[onlineit]

For general information about this feature, see SQL Pass-Through Facility.
Here are the SQL pass-through facility specifics for the DB2 under z/OS interface:
The DBMS-name is DB2.
The CONNECT statement is optional.
The interface supports connections to multiple databases.
The maximum length of an SQL statement is 1 megabyte.

 Learn SQL server training