wiki

The SELECT command in previous versions of the server allowed syntax that did not always conform to SQL syntax rules. With V6.3, the server conforms to SQL syntax rules in use by its database manager, the DB2® program. Some examples illustrate changes that you might need to make to SELECT statements that you use.

  LIKE predicate for a nested SELECT statement
  SELECT statements for time calculation
  The index_keyseq and index_order columns
  Access to database objects using the SELECT command
  Retrieval of information from more than one database table
  Results of the SELECT command for the DISK device class
  Extra spaces appearing in output
  Data types for arithmetic operations

You cannot use the LIKE predicate for a nested SELECT statement. For example, you receive an error if you use the LIKE predicate as in this statement:

select * from volumeusage where volume_name like (select distinct volume_name from volumeusage where node_name='node1')

Replace such usage with the in parameter, as in this statement:

select * from volumeusage where volume_name in (select distinct volume_name from volumeusage where node_name='node1')

Labeled duration cannot be compared in a SELECT statement. For example, the following statement results in an SQL error:

select * from actlog where (current_time-date_time) seconds <= 60 seconds

The following statements are examples of correct usage. To list the activity log entries for the last 60 seconds, use:

select * from actlog where TIMESTAMPDIFF(2,CHAR(current_timestamp-date_time)) <= 60

To list the activity log entries for the last 60 minutes, use:

select * from actlog where TIMESTAMPDIFF(4,CHAR(current_timestamp-date_time)) <= 60

The first argument can be either INTEGER or SMALLINT. Valid values of interval (the first argument) are:

 
1:   Fractions of a second
2:   Seconds
4:   Minutes
8:   Hours
16:  Days
32:  Weeks
64:  Months
128: Quarters
256: Years 

Example: The following example returns 4277, the number of minutes between two timestamps:

 TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2001-09-29-11.25.42.483219') - TIMESTAMP('2001-09-26-12.07.58.065497')))

The system catalog tables SYSCAT.COLUMNS and SYSCAT.TABLES are now processed by the database manager, instead of directly by the Tivoli® Storage Manager server. The INDEX_KEYSEQ and INDEX_ORDER columns are not available. Use the KEYSEQ column instead. For information, use the search string keyseq in the following information center: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp.

The database objects that can be accessed using the SELECT command are the same as for earlier versions of the server, with some additions for new functions in V6.3.

However, the SYSCAT.COLUMNS and SYSCAT.TABLES catalog tables now include all database objects that are known to the server, including some objects that cannot be accessed through the SELECT command. You receive an error message if a SELECT command includes an attempt to access one of these objects.

To retrieve information from more than one table, use a join process. Many types of join processes can be used. For example, the following command, which worked with earlier versions of the server, no longer works:

select entity,activity,sum(bytes),sum(end_time-start_time),sum(affected),sum(failed),sum(mediaw)from summary where -
entity in (select node_name from nodes) and cast((current_timestamp-start_time)hours as decimal)<24 group by entity,activity 

You can declare names for columns that are retrieved from multiple tables so that a conditional statement can be run with the results that you want from the SELECT command. For example:

select entity,activity,sum(bytes),sum(end_time-start_time),sum(affected),sum(failed),sum(mediaw)from summary su, nodes nd -
where su.entity=nd.node_name and cast((current_timestamp-start_time)hours as decimal)<24 group by entity,activity

Results when you use the SELECT command to get information from the DEVCLASSES table have changed slightly in V6.3 for the DISK device class.

In previous releases, the SHARED field was blank (null) for the DISK device class. In V6.3, the SHARED field contains the value NO. The SHARED field does not apply to the DISK device class, and the value NO can be ignored.

Spaces might appear in output where they did not appear before. If trailing spaces appear in your output, such as in the following tabschema output example, you can use the RTRIM scalar function to remove them.

dsmadmc -errorlogn=errorlog -id=admin -pa=admin -comma -dataonly=y 'select tabschema,tabname from tables'

SYSCAT  ,ATTRIBUTES
SYSCAT  ,AUDITPOLICIES
SYSCAT  ,AUDITUSE

For example, if you are writing scripts for automation and need to strip out the additional spaces, you can use the RTRIM scalar function:

select rtrim(tabschema) as tabschema, tabname from syscat.tables

Changes in how data types for arithmetic operations are handled might require changes to SELECT commands that worked in earlier versions of the server. For example, the following command causes an arithmetic overflow error because of the SUM statement:

select node_name,sum(capacity) as capacity,sum(capacity * (pct_util/100)) as used from filespaces group by node_name

To make the command compatible with V6.3, add the CAST function to convert the items in the SUM statement to decimal data types:

select node_name,sum(capacity) as capacity,sum(cast(capacity as decimal) * cast((pct_util/100) as decimal)) as used from -
filespaces group by node_name