sql query command

You can run SQL queries from the command line by using istool sql query command. You can view the results in a file that is in a comma-separated value (CSV) format.

Purpose

Use this command to run SQL queries on objects in the InfoSphere® Information Server metadata repository. Use the CMVIEWS and IGVIEWS schemas to create your SQL queries. For more information about these schemas, see SQL views for InfoSphere Information Governance Dashboard.

Note: You can run the istool sql query command only when the metadata repository is on IBM® DB2® Version 9.1 for Linux®, UNIX, and Windows.

Prerequisites

You must have the Information Governance Catalog Information Asset Administrator or Information Governance Catalog Glossary Administrator role to run the command.

The version of istool command framework must be the same version as IBM InfoSphere Information Governance Catalog.

You must have Write and Create access to the directory and file for the query results.

You must allocate more memory to the istool process. For more information, see technote In InfoSphere Information Server, the command line interface (istool) does not have sufficient memory to run.

Run the command from the client tier, or from the engine tier if InfoSphere Information Governance Catalog was installed there.

The preferred way to run this command is in the istool command-line window. If you run this command in your operating system command-line window or in scripts, you must add istool to the command name.

If you plan to run big queries, increase the timeout value as described in the following documents:

If the timeout value is too low, the exception

java.net.SocketTimeoutException: Read
timed out

might occur.

Command syntax

Optional parameters and optional values in the syntax are enclosed in brackets [ ].

sql query
authentication parameters
[generic parameters] 
-input file_with_SQL_query
-output query_results_file
[-includeheader]
[-saveview]
[-pagesize]

Parameters

authentication parameters

Required. All asset interchange commands use authentication parameters to connect to a specific installation of InfoSphere Information Server.

generic parameters

The generic parameters are available to all asset interchange commands. Use the generic parameters to request help on command syntax, to specify silent or verbose operation, and to use a script to run commands.

-input | -i SQL_query_file

Specifies the directory path and name of the input file that contains SQL query text. Base the query on the SQL views of InfoSphere Information Governance Dashboard. The SQL must be read-only and the metadata repository user must have permission to run the SQL.

The SQL query must also support the IBM Db2 Version 9.1 for Linux, UNIX, and Windows CREATE VIEW statement. For example, the SQL query cannot end with

ORDER
BY

or FETCH FIRST n ROWS ONLY statements.

Incorrect syntax with the ORDER BY statement:

select * from SOME_TABLE order by a_column

Correct syntax with the ORDER BY statement:

with tmp as (select * from SOME_TABLE order by a_column) select * from tmp

Incorrect syntax with the FETCH FIRST n ROWS ONLY statement:

select * from SOME_TABLE fetch first 10 rows only

Correct syntax with the FETCH FIRST n ROWS ONLY statement:

with tmp as (select * from SOME_TABLE fetch first 10 rows only) select * from tmp

For more information about the CREATE VIEW statement, see CREATE VIEW statement topic.

-output | -o query_results_file

Specifies the directory path and name of the output file with the results in a CSV format.

-includeheader | -head

Specifies that header row with column names is included.

-saveview | -sv

Specifies that the view that is created from the SQL query in the CMVIEWS schema is not dropped after the query is finished. Each new view is given a unique name, for example CMVIEWS."SQL_488137710". When you run the same query more than once with this option, the view is updated. If you want to drop the view, do it manually.

-pagesize | -ps page_size

The size of the request or response that is sent to the repository. The size is expressed in bytes. The default value is 1000000. The page size affects performance and memory consumption. The minimum acceptable value is 1000. Before you use this parameter, consult IBM Support.

Output

A return value of 0 indicates successful completion. Any other value indicates failure. The reason for the failure is displayed in a screen message.

For more information, see the system log file in either of the following directories:

For Microsoft Windows operating system environment: C:\Documents and Settings\username\istool_workspace\.metadata\.log; where username is the name of the operating system account of the user who runs this command.
For UNIX or Linux operating system environment: user_home/username/istool_workspace/.metadata/.log; where user_home is the root directory of all user accounts, and username is the name of the operating system account of the user who runs this command.

Example 1

This command runs a query that lists all terms, and saves the results to the CSV file.

sql query -authfile /tmp/auth_sample.txt -i ~/terms.sql -o /tmp/terms.out

The example output of the istool command:

SQL query export initiated...
SQL query export processed input sql. The number of rows to be exported: 3, estimated output file size: 866 bytes, sql processing time: 1149 ms.
SQL query export is starting writing into file /tmp/terms.out using page size 1000000 bytes.
SQL query export completed successfully. Total time elapsed: 1154 ms. Output file size: 866 bytes.

The example content of the input file terms.sql:

select * from IGVIEWS.IGBUSINESSTERM;

The example content of the output file terms.out:

"6662c0f2.e1b1ec6c.d595s14f8.fj5k9fv.adc4qh.cnhb8np09b426d5alfebh","2017-08-21 11:30:51.0","2017-08-21 11:37:22.0","isadmin","isadmin","25 things to do in Kraków","","","","0","","","1","","","6662c0f2.ee6a64fe.d595s148m.epp4vc0.1e03d1.sgle53h32insf2up2bkmp"
"6662c0f2.e1b1ec6c.d595s14bv.5go41p4.a1ks33.0l9rnpe1orkuhtjgtv6n0","2017-08-21 11:27:15.0","2017-08-21 12:50:26.0","isadmin","isadmin","The Name of the Rose","","","","0","","","1","","","6662c0f2.ee6a64fe.d595s148m.epp4vc0.1e03d1.sgle53h32insf2up2bkmp"
"6662c0f2.e1b1ec6c.d595s14jh.m4ere7c.d4lcs6.paagta8ul1hh3v7e75dlg","2017-08-21 11:35:32.0","2017-08-21 11:36:29.0","isadmin","isadmin","On the Origin of Species by Means of Natural Selection, or the Preservation of Favoured Races in the Struggle for Life","","","","0","","","1","","","6662c0f2.ee6a64fe.d595s148m.epp4vc0.1e03d1.sgle53h32insf2up2bkmp"

Example 2

This command runs a query that lists terms with custom attributes, saves the results to the CSV file, and adds the header row in the result file.

sql query -authfile /tmp/auth_sample.txt -i ~/terms_with_customattributes.sql -o /tmp/terms_with_ca.csv -includeheader

The example output of the istool command:

SQL query export initiated...
SQL query export processed input sql. The number of rows to be exported: 3, estimated output file size: 458 bytes, sql processing time: 1194 ms.
SQL query export is starting writing into file /tmp/terms_with_ca.csv using page size 1000000 bytes.
SQL query export completed successfully. Total time elapsed: 1198 ms. Output file size: 458 bytes.

The example content of the input file terms_with_customattributes.sql:

WITH CustomAttributeSingleValues AS (
	SELECT varchar(VALUE) value, OBJECTRID, OFCUSTOMATTRIBUTERID, OFCUSTOMARRAYVALRID  FROM CMVIEWS.COMMONCUSTOMATTRIBUTETEXTVAL
	UNION ALL 
	SELECT varchar(VALUE) value, OBJECTRID, OFCUSTOMATTRIBUTERID, OFCUSTOMARRAYVALRID  FROM CMVIEWS.COMMONCUSTOMATTRIBUTESTRINGVAL
	UNION ALL 
	SELECT varchar(VALUE) value, OBJECTRID, OFCUSTOMATTRIBUTERID, OFCUSTOMARRAYVALRID  FROM CMVIEWS.COMMONCUSTOMATTRIBUTEINTVAL
	UNION ALL 
	SELECT varchar(VALUE) value, OBJECTRID, OFCUSTOMATTRIBUTERID, OFCUSTOMARRAYVALRID  FROM CMVIEWS.COMMONCUSTOMATTRIBUTEDOUBLEVAL
	UNION ALL 
	SELECT varchar(VALUE) value, OBJECTRID, OFCUSTOMATTRIBUTERID, OFCUSTOMARRAYVALRID  FROM CMVIEWS.COMMONCUSTOMATTRIBUTEDATEVAL
	UNION ALL 
	SELECT varchar(VALUE) value, OBJECTRID, OFCUSTOMATTRIBUTERID, OFCUSTOMARRAYVALRID  FROM CMVIEWS.COMMONCUSTOMATTRIBUTEBOOLVAL
)
,CustomAttributeMultiValues AS (
	SELECT VARCHAR(value) value, arr.OBJECTRID, arr.OFCUSTOMATTRIBUTERID
	FROM CustomAttributeSingleValues single 
	JOIN CMVIEWS.COMMONCUSTOMATTRIBUTEARRAYVAL arr ON (single.OFCUSTOMARRAYVALRID=arr.RID)
)
,CustomAttributeValues AS ( 
	SELECT VALUE, OBJECTRID, OFCUSTOMATTRIBUTERID FROM CustomAttributeSingleValues WHERE OFCUSTOMARRAYVALRID IS null
	UNION ALL
	SELECT VALUE, OBJECTRID, OFCUSTOMATTRIBUTERID FROM CustomAttributeMultiValues
)
select t.NAME as NAME, cat.NAME as CATEGORY,
	max(decode(cadef.NAME, 'Author', caval.VALUE)) AS Author,
	max(decode(cadef.NAME, 'Return Date', caval.VALUE)) AS ReturnDate,
	max(decode(cadef.NAME, 'Price', caval.VALUE)) AS Price,
	listagg(decode(cadef.NAME, 'Genre', caval.VALUE), ',') AS Genre 
from IGVIEWS.IGBUSINESSTERM t
join IGVIEWS.IGBUSINESSCATEGORY cat on (t.OFCATEGORY=cat.RID)
left join CustomAttributeValues caval ON (caval.OBJECTRID=t.RID)
left join CMVIEWS.COMMONCUSTOMATTRIBUTE cadef on (cadef.RID=caval.OFCUSTOMATTRIBUTERID)
GROUP BY t.NAME, cat.NAME ;

The example content of the output file terms_with_ca.csv:

"NAME","CATEGORY","AUTHOR","RETURNDATE","PRICE","GENRE"
"25 things to do in Kraków","Book","Joint publication","2017-07-31-22.00.00.000000","1.25E1","Guide"
"On the Origin of Species by Means of Natural Selection, or the Preservation of Favoured Races in the Struggle for Life","Book","Charles Darwin","2017-08-30-22.00.00.000000","9.97E1","Science"
"The Name of the Rose","Book","Umberto Eco","2017-08-16-22.00.00.000000","5.389E1","Detective story,Novel"