How to output Sybase query results to csv file and run queries via command line

  • 7011891
  • 06-Mar-2013
  • 15-Aug-2017

Environment


Novell ZENworks Configuration Management 11.2 Database
Novell ZENworks Configuration Management 11.3 Database
Novell ZENworks Configuration Management 11.4 Database

Situation

  • Running dbisql from the command line
  • Need to automatically export the results of a query
  • Need to run queries from a .sql file with only command line access
  • Need to run queries on Sybase on the ZENworks appliance

Resolution

It is possible to run dbisql in a command-prompt mode, with no windowed user interface, making it possible to export data, and even to update rows in the database.

Specify option "-nogui" when running Sybase dbisql from the command line, e.g.:
/opt/novell/zenworks/share/sybase/bin32s/dbisql -nogui -C "eng=<database_name>;dbn=<database_name>;uid=<db_admin_user>;pwd=<password>" "<query>; OUTPUT TO <output_file> FORMAT ASCII"

To connect to an already running database through the network, add -host and -port options and remove eng and dbn connection parameters, e.g.:
/opt/novell/zenworks/share/sybase/bin32s/dbisql -nogui -host <dns_name_or_ip_address> -port <port> -C "uid=<db_admin_user>;pwd=<password>" "<query>; OUTPUT TO <output_file> FORMAT ASCII"

To run queries from a .sql file, specify the file instead of the query string, e.g.:
/opt/novell/zenworks/share/sybase/bin32s/dbisql -nogui -host <dns_name_or_ip_address> -port <port> -C "uid=<db_admin_user>;pwd=<password>" query.sql

Examples:
/opt/novell/zenworks/share/sybase/bin32s/dbisql -nogui -C "eng=zenworks_NTS_LAB;dbn=zenworks_NTS_LAB;uid=zenadmin;pwd=Zw0#6378d4d2d0ef955090ed9db48" "select * from zZENObject; OUTPUT TO zZENObject.txt FORMAT ASCII"

/opt/novell/zenworks/share/sybase/bin32s/dbisql -nogui -host 127.0.0.1 -port 2638 -C "uid=zenadmin;pwd=Zw0#6378d4d2d0ef955090ed9db48" "select * from zZENObject; OUTPUT TO zZENObject.txt FORMAT ASCII"

/opt/novell/zenworks/share/sybase/bin32s/dbisql -nogui -host 127.0.0.1 -port 2638 -C "uid=zenadmin;pwd=Zw0#6378d4d2d0ef955090ed9db48" query.sql

NOTE: The first two examples will generate a CSV file named "zZENObject.txt" at the current folder.

Additional Information

More information:

ZENworks Database: To get the zenadmin password use zman dgc.

NOTE: 11.3.x introduced the Audit database - modify examples above for reports from Audit database.
ZENworks Audit Database: To get the zenauditadmin password use zman dgca.

NOTE: In ZENworks 2017, Sybase is 64-bit. For 64-bit Sybase, the dbisql utility is at /opt/novell/zenworks/share/sybase/bin64s/dbisql