Table Action Set: Syntax

Provides actions for accessing and managing data

partition Action

Partitions a table.

Note:	You cannot append rows to a table that is partitioned and ordered.
Example:	Partition a Table

CASL Syntax

table.partition <result=results> <status=rc> /

casout={

caslib="string",

compress=TRUE | FALSE,

indexVars={"variable-name-1" <, "variable-name-2", ...>},

label="string",

lifetime=64-bit-integer,

maxMemSize=64-bit-integer,

memoryFormat="DVR" | "INHERIT" | "STANDARD",

name="table-name",

promote=TRUE | FALSE,

replace=TRUE | FALSE,

replication=integer,

threadBlockSize=64-bit-integer,

timeStamp="string"

table={

caslib="string",

computedOnDemand=TRUE | FALSE,

computedVars={{

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

}, {...}},

computedVarsProgram="string",

dataSourceOptions={key-1=any-list-or-data-type-1 <, key-2=any-list-or-data-type-2, ...>},

groupBy={{

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

}, {...}},

groupByMode="NOSORT" | "REDISTRIBUTE",

importOptions={fileType="ANY" | "AUDIO" | "AUTO" | "BASESAS" | "CSV" | "DELIMITED" | "DOCUMENT" | "DTA" | "ESP" | "EXCEL" | "FMT" | "HDAT" | "IMAGE" | "JMP" | "LASR" | "PARQUET" | "SOUND" | "SPSS" | "VIDEO" | "XLS", fileType-specific-parameters},

name="table-name",

onDemand=TRUE | FALSE,

orderBy={{

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

}, {...}},

singlePass=TRUE | FALSE,

vars={{

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

}, {...}},

where="where-expression",

whereTable={

casLib="string"

dataSourceOptions={adls_noreq-parameters | bigquery-parameters | cas_noreq-parameters | clouddex-parameters | db2-parameters | dnfs-parameters | esp-parameters | fedsvr-parameters | gcs_noreq-parameters | greenplum-parameters | hadoop-parameters | hana-parameters | hdfs-parameters | impala-parameters | jdbc-parameters | mongodb-parameters | mysql-parameters | netezza-parameters | odbc-parameters | oracle-parameters | path-parameters | postgres-parameters | redshift-parameters | s3-parameters | sforce-parameters | snowflake-parameters | spark-parameters | spde-parameters | sqlserver-parameters | teradata-parameters | vertica-parameters | yellowbrick-parameters}

name="table-name"

vars={{

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

}, {...}}

where="where-expression"

}

;

indicates a required parameter

Summary: Input and Output Tables

If a row includes a subparameter, you can specify the name, caslib, and so on in the subparameter. Otherwise, you can specify the name, caslib, and so on in the parameter.

Parameters for Reading Input Tables
Parameter	Subparameter	Description
required parametertable	—	specifies the name of the input table.

Parameters for Creating Output Tables
Parameter	Subparameter	Description
casout	—	specifies the settings for an output table.

Parameter Descriptions

casout={casouttable}

specifies the settings for an output table.

Alias	outTable

Long form	casout={name="table-name"}
Shortcut form	casout="table-name"

The casouttable value can be one or more of the following:

caslib="string"

specifies the name of the caslib for the output table.

compress=TRUE | FALSE

when set to True, applies data compression to the table.

Default	FALSE

indexVars={"variable-name-1" <, "variable-name-2", ...>}

specifies the list of variables to create indexes for in the output data.

label="string"

specifies the descriptive label to associate with the table.

lifetime=64-bit-integer

specifies the number of seconds to keep the table in memory after it is last accessed. The table is dropped if it is not accessed for the specified number of seconds.

Default	0
Minimum value	0

maxMemSize=64-bit-integer

specifies the maximum amount of memory, in bytes, that each thread should allocate for in-memory blocks before converting to a memory-mapped file. Files are written in the directories that are specified in the CAS_DISK_CACHE environment variable.

TIP	You can enclose the value in quotation marks and specify B, K, M, G, or T as a suffix to indicate the units. For example, "8M" specifies eight megabytes.

memoryFormat="DVR" | "INHERIT" | "STANDARD"

specifies the memory format for the output table.

Default	INHERIT

DVR

use the duplicate value reduction memory format. This memory format can reduce the memory consumption and file size when the input data contains duplicate values.

INHERIT

use the default memory format that is set for the server. By default, the server uses the standard memory format. If an administrator sets the CAS_DEFAULT_MEMORY_FORMAT environment variable to DVR, then the DVR memory format is set as the default for the server.

STANDARD

use the standard memory format.

name="table-name"

specifies the name for the output table.

promote=TRUE | FALSE

when set to True, adds the output table with a global scope. This enables other sessions to access the table, subject to access controls. The target caslib must also have a global scope.

Default	FALSE

replace=TRUE | FALSE

when set to True, overwrites an existing table that has the same name.

Default	FALSE

replication=integer

specifies the number of copies of the table to make for fault tolerance. Larger values result in slower performance and use more memory, but provide high availability for data in the event of a node failure. Data redundancy applies to distributed servers only.

Default	1
Minimum value	0

threadBlockSize=64-bit-integer

specifies the number of bytes to use for blocks in the output table. The blocks are read by threads. Gradually increase this value when you have a large table with millions or billions of rows and you are tuning for performance. Larger values can increase performance with indexed tables. However, if the value is too large, then you can cause thread starvation due to too few blocks for threads to work on.

Alias	blockSize
Default	1048576
Minimum value	0
TIP	You can enclose the value in quotation marks and specify B, K, M, G, or T as a suffix to indicate the units. For example, "8M" specifies eight megabytes.

timeStamp="string"

specifies to add a timestamp column to the table. Support for timeStamp is action-specific. Specify the value in the form that is appropriate for your session locale.

* table={castable}

specifies the name of the input table.

For more information about specifying the table parameter, see the common castable parameter.

Result Descriptions

averageShuffleWaitTime

The result is a double. You can access the value from results.averageShuffleWaitTime.

caslib

The result is a string. You can access the value from results.caslib.

maxShuffleWaitTime

The result is a double. You can access the value from results.maxShuffleWaitTime.

minShuffleWaitTime

The result is a double. You can access the value from results.minShuffleWaitTime.

rowsTransferred

The result is a 64-bit integer. You can access the value from results.rowsTransferred.

shuffleWaitTime

The result is a double. You can access the value from results.shuffleWaitTime.

tableName

The result is a string. You can access the value from results.tableName.

partition Action

Partitions a table.

Note:	You cannot append rows to a table that is partitioned and ordered.
Example:	Partition a Table

Lua Syntax

results, info = s:table_partition{

casout={

caslib="string",

compress=true | false,

indexVars={"variable-name-1" <, "variable-name-2", ...>},

label="string",

lifetime=64-bit-integer,

maxMemSize=64-bit-integer,

memoryFormat="DVR" | "INHERIT" | "STANDARD",

name="table-name",

promote=true | false,

replace=true | false,

replication=integer,

threadBlockSize=64-bit-integer,

timeStamp="string"

table={

caslib="string",

computedOnDemand=true | false,

computedVars={{

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

}, {...}},

computedVarsProgram="string",

dataSourceOptions={key-1=any-list-or-data-type-1 <, key-2=any-list-or-data-type-2, ...>},

groupBy={{

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

}, {...}},

groupByMode="NOSORT" | "REDISTRIBUTE",

name="table-name",

onDemand=true | false,

orderBy={{

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

}, {...}},

singlePass=true | false,

vars={{

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

}, {...}},

where="where-expression",

whereTable={

casLib="string"

name="table-name"

vars={{

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

}, {...}}

where="where-expression"

}

indicates a required parameter

Summary: Input and Output Tables

If a row includes a subparameter, you can specify the name, caslib, and so on in the subparameter. Otherwise, you can specify the name, caslib, and so on in the parameter.

Parameters for Reading Input Tables
Parameter	Subparameter	Description
required parametertable	—	specifies the name of the input table.

Parameters for Creating Output Tables
Parameter	Subparameter	Description
casout	—	specifies the settings for an output table.

Parameter Descriptions

casout={casouttable}

specifies the settings for an output table.

Alias	outTable

Long form	casout={name="table-name"}
Shortcut form	casout="table-name"

The casouttable value can be one or more of the following:

caslib="string"

specifies the name of the caslib for the output table.

compress=true | false

when set to True, applies data compression to the table.

Default	false

indexVars={"variable-name-1" <, "variable-name-2", ...>}

specifies the list of variables to create indexes for in the output data.

label="string"

specifies the descriptive label to associate with the table.

lifetime=64-bit-integer

specifies the number of seconds to keep the table in memory after it is last accessed. The table is dropped if it is not accessed for the specified number of seconds.

Default	0
Minimum value	0

maxMemSize=64-bit-integer

TIP	You can enclose the value in quotation marks and specify B, K, M, G, or T as a suffix to indicate the units. For example, "8M" specifies eight megabytes.

memoryFormat="DVR" | "INHERIT" | "STANDARD"

specifies the memory format for the output table.

Default	INHERIT

DVR

use the duplicate value reduction memory format. This memory format can reduce the memory consumption and file size when the input data contains duplicate values.

INHERIT

STANDARD

use the standard memory format.

name="table-name"

specifies the name for the output table.

promote=true | false

when set to True, adds the output table with a global scope. This enables other sessions to access the table, subject to access controls. The target caslib must also have a global scope.

Default	false

replace=true | false

when set to True, overwrites an existing table that has the same name.

Default	false

replication=integer

Default	1
Minimum value	0

threadBlockSize=64-bit-integer

Alias	blockSize
Default	1048576
Minimum value	0
TIP	You can enclose the value in quotation marks and specify B, K, M, G, or T as a suffix to indicate the units. For example, "8M" specifies eight megabytes.

timeStamp="string"

specifies to add a timestamp column to the table. Support for timeStamp is action-specific. Specify the value in the form that is appropriate for your session locale.

* table={castable}

specifies the name of the input table.

For more information about specifying the table parameter, see the common castable parameter.

Result Descriptions

averageShuffleWaitTime

The result is a double. You can access the value from results.averageShuffleWaitTime.

caslib

The result is a string. You can access the value from results.caslib.

maxShuffleWaitTime

The result is a double. You can access the value from results.maxShuffleWaitTime.

minShuffleWaitTime

The result is a double. You can access the value from results.minShuffleWaitTime.

rowsTransferred

The result is a 64-bit integer. You can access the value from results.rowsTransferred.

shuffleWaitTime

The result is a double. You can access the value from results.shuffleWaitTime.

tableName

The result is a string. You can access the value from results.tableName.

partition Action

Partitions a table.

Note:	You cannot append rows to a table that is partitioned and ordered.
Example:	Partition a Table

Python Syntax

results= s.table.partition(

casout={

"caslib":"string",

"compress":True | False,

"indexVars":["variable-name-1" <, "variable-name-2", ...>],

"label":"string",

"lifetime":64-bit-integer,

"maxMemSize":64-bit-integer,

"memoryFormat":"DVR" | "INHERIT" | "STANDARD",

"name":"table-name",

"promote":True | False,

"replace":True | False,

"replication":integer,

"threadBlockSize":64-bit-integer,

"timeStamp":"string"

table={

"caslib":"string",

"computedOnDemand":True | False,

"computedVars":[{

"format":"string",

"formattedLength":integer,

"label":"string",

"name":"variable-name",

"nfd":integer,

"nfl":integer

}<, {...}>],

"computedVarsProgram":"string",

"dataSourceOptions":{"key-1":{any-list-or-data-type-1} <, "key-2":{any-list-or-data-type-2}, ...>},

"groupBy":[{

"format":"string",

"formattedLength":integer,

"label":"string",

"name":"variable-name",

"nfd":integer,

"nfl":integer

}<, {...}>],

"groupByMode":"NOSORT" | "REDISTRIBUTE",

"importOptions":{"fileType":"ANY" | "AUDIO" | "AUTO" | "BASESAS" | "CSV" | "DELIMITED" | "DOCUMENT" | "DTA" | "ESP" | "EXCEL" | "FMT" | "HDAT" | "IMAGE" | "JMP" | "LASR" | "PARQUET" | "SOUND" | "SPSS" | "VIDEO" | "XLS", fileType-specific-parameters},

"name":"table-name",

"onDemand":True | False,

"orderBy":[{

"format":"string",

"formattedLength":integer,

"label":"string",

"name":"variable-name",

"nfd":integer,

"nfl":integer

}<, {...}>],

"singlePass":True | False,

"vars":[{

"format":"string",

"formattedLength":integer,

"label":"string",

"name":"variable-name",

"nfd":integer,

"nfl":integer

}<, {...}>],

"where":"where-expression",

"whereTable":{

"casLib":"string"

"dataSourceOptions":{adls_noreq-parameters | bigquery-parameters | cas_noreq-parameters | clouddex-parameters | db2-parameters | dnfs-parameters | esp-parameters | fedsvr-parameters | gcs_noreq-parameters | greenplum-parameters | hadoop-parameters | hana-parameters | hdfs-parameters | impala-parameters | jdbc-parameters | mongodb-parameters | mysql-parameters | netezza-parameters | odbc-parameters | oracle-parameters | path-parameters | postgres-parameters | redshift-parameters | s3-parameters | sforce-parameters | snowflake-parameters | spark-parameters | spde-parameters | sqlserver-parameters | teradata-parameters | vertica-parameters | yellowbrick-parameters}

"name":"table-name"

"vars":[{

"format":"string",

"formattedLength":integer,

"label":"string",

"name":"variable-name",

"nfd":integer,

"nfl":integer

}<, {...}>]

"where":"where-expression"

}

)

indicates a required parameter

Summary: Input and Output Tables

If a row includes a subparameter, you can specify the name, caslib, and so on in the subparameter. Otherwise, you can specify the name, caslib, and so on in the parameter.

Parameters for Reading Input Tables
Parameter	Subparameter	Description
required parametertable	—	specifies the name of the input table.

Parameters for Creating Output Tables
Parameter	Subparameter	Description
casout	—	specifies the settings for an output table.

Parameter Descriptions

casout={casouttable}

specifies the settings for an output table.

Alias	outTable

Long form	casout={"name":"table-name"}
Shortcut form	casout="table-name"

The casouttable value can be one or more of the following:

"caslib":"string"

specifies the name of the caslib for the output table.

"compress":True | False

when set to True, applies data compression to the table.

Default	False

"indexVars":["variable-name-1" <, "variable-name-2", ...>]

specifies the list of variables to create indexes for in the output data.

"label":"string"

specifies the descriptive label to associate with the table.

"lifetime":64-bit-integer

specifies the number of seconds to keep the table in memory after it is last accessed. The table is dropped if it is not accessed for the specified number of seconds.

Default	0
Minimum value	0

"maxMemSize":64-bit-integer

TIP	You can enclose the value in quotation marks and specify B, K, M, G, or T as a suffix to indicate the units. For example, "8M" specifies eight megabytes.

"memoryFormat":"DVR" | "INHERIT" | "STANDARD"

specifies the memory format for the output table.

Default	INHERIT

DVR

use the duplicate value reduction memory format. This memory format can reduce the memory consumption and file size when the input data contains duplicate values.

INHERIT

STANDARD

use the standard memory format.

"name":"table-name"

specifies the name for the output table.

"promote":True | False

when set to True, adds the output table with a global scope. This enables other sessions to access the table, subject to access controls. The target caslib must also have a global scope.

Default	False

"replace":True | False

when set to True, overwrites an existing table that has the same name.

Default	False

"replication":integer

Default	1
Minimum value	0

"threadBlockSize":64-bit-integer

Alias	blockSize
Default	1048576
Minimum value	0
TIP	You can enclose the value in quotation marks and specify B, K, M, G, or T as a suffix to indicate the units. For example, "8M" specifies eight megabytes.

"timeStamp":"string"

specifies to add a timestamp column to the table. Support for timeStamp is action-specific. Specify the value in the form that is appropriate for your session locale.

* table={castable}

specifies the name of the input table.

For more information about specifying the table parameter, see the common castable parameter.

Result Descriptions

averageShuffleWaitTime

The result is a double. You can access the value from results.averageShuffleWaitTime.

caslib

The result is a string. You can access the value from results.caslib.

maxShuffleWaitTime

The result is a double. You can access the value from results.maxShuffleWaitTime.

minShuffleWaitTime

The result is a double. You can access the value from results.minShuffleWaitTime.

rowsTransferred

The result is a 64-bit integer. You can access the value from results.rowsTransferred.

shuffleWaitTime

The result is a double. You can access the value from results.shuffleWaitTime.

tableName

The result is a string. You can access the value from results.tableName.

partition Action

Partitions a table.

Note:	You cannot append rows to a table that is partitioned and ordered.
Example:	Partition a Table

R Syntax

results <– cas.table.partition(s,

casout=list(

caslib="string",

compress=TRUE | FALSE,

indexVars=list("variable-name-1" <, "variable-name-2", ...>),

label="string",

lifetime=64-bit-integer,

maxMemSize=64-bit-integer,

memoryFormat="DVR" | "INHERIT" | "STANDARD",

name="table-name",

promote=TRUE | FALSE,

replace=TRUE | FALSE,

replication=integer,

threadBlockSize=64-bit-integer,

timeStamp="string"

table=list(

caslib="string",

computedOnDemand=TRUE | FALSE,

computedVars=list( list(

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

) <, list(...)>),

computedVarsProgram="string",

dataSourceOptions=list(key-1=list(any-list-or-data-type-1) <, key-2=list(any-list-or-data-type-2), ...>),

groupBy=list( list(

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

) <, list(...)>),

groupByMode="NOSORT" | "REDISTRIBUTE",

name="table-name",

onDemand=TRUE | FALSE,

orderBy=list( list(

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

) <, list(...)>),

singlePass=TRUE | FALSE,

vars=list( list(

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

) <, list(...)>),

where="where-expression",

whereTable=list(

casLib="string"

name="table-name"

vars=list( list(

format="string",

formattedLength=integer,

label="string",

name="variable-name",

nfd=integer,

nfl=integer

) <, list(...)>)

where="where-expression"

)

indicates a required parameter

Summary: Input and Output Tables

If a row includes a subparameter, you can specify the name, caslib, and so on in the subparameter. Otherwise, you can specify the name, caslib, and so on in the parameter.

Parameters for Reading Input Tables
Parameter	Subparameter	Description
required parametertable	—	specifies the name of the input table.

Parameters for Creating Output Tables
Parameter	Subparameter	Description
casout	—	specifies the settings for an output table.

Parameter Descriptions

casout=list(casouttable)

specifies the settings for an output table.

Alias	outTable

Long form	casout=list(name="table-name")
Shortcut form	casout="table-name"

The casouttable value can be one or more of the following:

caslib="string"

specifies the name of the caslib for the output table.

compress=TRUE | FALSE

when set to True, applies data compression to the table.

Default	FALSE

indexVars=list("variable-name-1" <, "variable-name-2", ...>)

specifies the list of variables to create indexes for in the output data.

label="string"

specifies the descriptive label to associate with the table.

lifetime=64-bit-integer

specifies the number of seconds to keep the table in memory after it is last accessed. The table is dropped if it is not accessed for the specified number of seconds.

Default	0
Minimum value	0

maxMemSize=64-bit-integer

TIP	You can enclose the value in quotation marks and specify B, K, M, G, or T as a suffix to indicate the units. For example, "8M" specifies eight megabytes.

memoryFormat="DVR" | "INHERIT" | "STANDARD"

specifies the memory format for the output table.

Default	INHERIT

DVR

use the duplicate value reduction memory format. This memory format can reduce the memory consumption and file size when the input data contains duplicate values.

INHERIT

STANDARD

use the standard memory format.

name="table-name"

specifies the name for the output table.

promote=TRUE | FALSE

when set to True, adds the output table with a global scope. This enables other sessions to access the table, subject to access controls. The target caslib must also have a global scope.

Default	FALSE

replace=TRUE | FALSE

when set to True, overwrites an existing table that has the same name.

Default	FALSE

replication=integer

Default	1
Minimum value	0

threadBlockSize=64-bit-integer

Alias	blockSize
Default	1048576
Minimum value	0
TIP	You can enclose the value in quotation marks and specify B, K, M, G, or T as a suffix to indicate the units. For example, "8M" specifies eight megabytes.

timeStamp="string"

specifies to add a timestamp column to the table. Support for timeStamp is action-specific. Specify the value in the form that is appropriate for your session locale.

* table=list(castable)

specifies the name of the input table.

For more information about specifying the table parameter, see the common castable parameter.

Result Descriptions

averageShuffleWaitTime

The result is a double. You can access the value from results.averageShuffleWaitTime.

caslib

The result is a string. You can access the value from results.caslib.

maxShuffleWaitTime

The result is a double. You can access the value from results.maxShuffleWaitTime.

minShuffleWaitTime

The result is a double. You can access the value from results.minShuffleWaitTime.

rowsTransferred

The result is a 64-bit integer. You can access the value from results.rowsTransferred.

shuffleWaitTime

The result is a double. You can access the value from results.shuffleWaitTime.

tableName

The result is a string. You can access the value from results.tableName.

Last updated: October 18, 2022