SAP Sybase IQ

To create a PDM with support for features specific to the SAP® Sybase® IQ DBMS family, select the appropriate version in the DBMS field of the New Model dialog. To view these extensions to the PowerDesigner metamodel in the Resource Editor, select Database > Edit Current DBMS and expand the Profile node.

Note: The DBMS definition files for Sybase IQ v12.x, v15.0, and v15.1 are deprecated.

The following sections list the extensions provided for IQ.

Note: We do not provide documentation for the properties on the Physical Options and certain other tabs, though minimal information is available for them in the Resource Editor. For information about these properties, consult your DBMS reference documentation.

Tables

The following extensions are available on the Sybase IQ tab (v12.4.3 and higher):

Name	Description
DBSpace	[v15.0 and higher] Specifies the dbspace in which to create the table (see Dbspaces (IQ)). Scripting name: DBSpace
Global temporary table	[v12.4.3 to 15.2] Specifies that the table is a global temporary table. Scripting name: ExtGlobalTemporaryTable
Scope	[v15.3 and higher] Specifies that the table is either a global or local temporary table. Scripting name: TemporaryTableScope
On commit	[v15.0 and higher] Action on commit. Scripting name: OnCommit
Not transactional	[v15.0 and higher] A table created using NOT TRANSACTIONAL is not affected by either COMMIT or ROLLBACK. Scripting name: NotTransactional
Remote location	[v15.0 and higher] Used to create a table at the remote location. Scripting name: At
Partition key	[v15.0 and higher] Specifies the partition key column. Scripting name: PartitionKey

Columns

The following extensions are available on the Sybase tab (v12.4.3 and higher):

Name	Description
DBSpace	[v15.4 and higher] Specifies the database file (dbspace) in which to create the column (see Dbspaces (IQ)). Scripting name: DBSpace
Number of distinct value (Iq unique)	Defines the cardinality of the column (to optimize the indexes internally). Scripting name: ExtIqUnicity

Name

Description

DBSpace

[v15.4 and higher] Specifies the database file (dbspace) in which to create the column (see Dbspaces (IQ)).

Scripting name: DBSpace

Number of distinct value (Iq unique)

Defines the cardinality of the column (to optimize the indexes internally).

Scripting name: ExtIqUnicity

In addition, from v15.0 and higher, the Partitions tab allows you to override the allocations of partitioned column values to different dbspaces (see Table and Column Partitions (IQ)).

Indexes

The following extensions are available on the Sybase tab (v15.0 and higher):

Name	Description
With nulls not distinct	[v15.4 and higher, when Unique] Specifies that more than one null value is permitted despite the index requiring unique values. Scripting name: WithNullsNotDistinct
Tablespace	[Non-text indexes] Specifies the index dbspace (see Dbspaces (IQ)). Scripting name: In
Notify	[Non-text indexes] Gives notification messages after n records are successfully added for the index. Scripting name: Notify
Word length	[WD indexes] Specifies the maximum word length that is permitted in the WD index. Scripting name: Limit
Delimited by	[WD indexes] Specifies separators to use in parsing a column string into the words to be stored in that column's WD index. Scripting name: DelimitedBy
Configuration	[Text indexes] Specifies the text configuration (see Text Configurations (IQ/SQL Anywhere)) to be used to control the building of the text index. Scripting name: Configuration
Immediate refresh	[Text indexes v15.2 and higher] Specifies that the index is refreshed immediately each time data is written to the table. Scripting name: Refresh

Keys and References

The following extensions are available on the General tab (v15.0 and higher):

Name	Description
DBSpace	Specifies the DBSpace where the object is stored (see Dbspaces (IQ)). Scripting name: PortNumber

Name

Description

DBSpace

Specifies the DBSpace where the object is stored (see Dbspaces (IQ)).

Scripting name: PortNumber

Data Sources

The following extensions are available on the Data Movement (Lifecycle) tab (v15.0 and higher), and are required when the first phase of a lifecycle policy must manage data in an external database:

Name	Description
Remote server name	Specifies the name of the server where the remote database is located. Scripting name: Server
Remote database name	Specifies the name of the remote database from which data must be loaded. Scripting name: DatabaseName
Server class	Specifies the type of connection that must be made to the external database. Select the appropriate value from the list. Scripting name: ServerClass
Connection string	Specifies the connection string used to connect to the external database in the format: JDBC - `<host>:<port>[/database name]` ODBC - `<odbc name>` Scripting name: JDBCConnectionString/ODBCConnectionString
User/group	Specifies the user or group name with which to log into the external database. Scripting name: ExternalLogin

Procedures

The following extensions are available on the Sybase IQ tab (v15.0 and higher):

Name	Description
Temporary	[standard functions] Specifies that the function is visible only by the connection that created it, and that it is automatically dropped when the connection is dropped. Scripting name: TempFunction
Return data type	Specifies the procedure return data type. Scripting name: ReturnDttp
Routine characteristics	[standard functions] Transact-SQL-like error handling and deterministic options. Scripting name: RoutineCharacteristics
Sql security	[standard functions] Defines whether the function is executed as the INVOKER, the user who is calling the function, or as the DEFINER, the user who owns the function. Scripting name: SqlSecurity
URL	[web functions] Specifies the URL of the web service. Scripting name: URL
Type	[web functions] Specifies the format used when making the web service request. Scripting name: URLType
Header	[HTTP web functions] When creating HTTP web service client functions, use this clause to add or modify HTTP request header entries. Scripting name: Header
Soap header	[SOAP web functions] When declaring a SOAP web service as a function, use this clause to specify one or more SOAP request header entries. Scripting name: SoapHeader
Certificate	[web functions] To make a secure (HTTPS) request, a client must have access to the certificate used by the HTTPS server. The necessary information is specified in a string of semicolon-separated key/value pairs. Scripting name: Certificate
Client port	[HTTP web functions] Identifies the port number on which the HTTP client procedure communicates using TCP/IP. Scripting name: ClientPort
Namespace	[SOAP web functions] Identifies the method namespace usually required for both SOAP:RPC and SOAP:DOC requests. Scripting name: Namespace
Proxy	[web functions] Specifies the URI of a proxy server. Scripting name: Proxy

Users

The following extensions are available on the General tab (v15.0 and higher):

Name	Description
Force change	Controls whether the user must specify a new password when they log in. This setting overrides the password_expiry_on_next_login option setting in the login policy. Scripting name: ForcePasswordChange
Login policy	Specifies the login policy to assign to the user (see Login Policies (IQ/SQL Anywhere)). Scripting name: LoginPolicy

Name

Description

Force change

Controls whether the user must specify a new password when they log in. This setting overrides the password_expiry_on_next_login option setting in the login policy.

Scripting name: ForcePasswordChange

Specifies the login policy to assign to the user (see Login Policies (IQ/SQL Anywhere)).

Scripting name: LoginPolicy

Web Services

The following extensions are available on the Sybase tab (v12.6 and higher):

Name	Description
Port number	Specifies the web service port number. Scripting name: PortNumber
Server name	Specifies the web service server name. Scripting name: ServerName
Name prefix	[DISH service type] Specifies a name prefix. Only SOAP services whose names begin with this prefix are handled. Scripting name: Prefix

Name

Description

Port number

Specifies the web service port number.

Scripting name: PortNumber

Server name

Specifies the web service server name.

Scripting name: ServerName

Name prefix

[DISH service type] Specifies a name prefix. Only SOAP services whose names begin with this prefix are handled.

Scripting name: Prefix

Web Operations

The following extensions are available on the Sybase tab (v12.6 and higher) when the service type is not dish:

Name	Description
URL	Determines whether URI paths are accepted and, if so, how they are processed. Scripting name: Url

Reference Architecture Modeling (IQ)
PowerDesigner provides a special EAM model to help you determine the architecture required to deploy a Sybase IQ data warehouse solution to meet your anticipated workload. An advisor wizard generates architectures based on one or more hardware servers, and comparison tools help you choose the best architecture based on your requirements for cost and speed.
Information Lifecycle Management (IQ)
Sybase IQ v15.0 and higher provides data placement capabilities and supports hierarchical storage management with relocation of less critical data to cheaper storage. PowerDesigner offers a simple modeling structure to cost effectively manage "aging" of data inside the data center from 1st tier high performance storage for frequently accessed data through 2nd tier near-line storage for data that is infrequently accessed to 3rd tier archive storage for data that must remain available for regulatory audits.
Events (IQ/SQL Anywhere)
Sybase IQ (v12.7 and higher) and SQL Anywhere (v10 and higher) support events, which allow you to automate and schedule actions. PowerDesigner models events as extended objects with a stereotype of <<Event>>.
Dbspaces (IQ)
Sybase IQ distributes user data across multiple disks at the application level by representing each device as a dbspace. A dbspace can be an operating system file or a raw disk partition. Dbspaces can contain both user data and internal database structures used for startup, recovery, backup, and transaction management.
Table and Column Partitions (IQ)
A partition is a physical division of the contents of a database table, based on values in the column designated as the partition key, and allocated to a particular dbspace. You can override the allocation of values in certain columns by specifying column partitions.
Logical Servers and Policies (IQ)
Sybase IQ v16 and higher supports logical servers, which provide the only means to access the multiplex server nodes. PowerDesigner models logical servers and logical server policies as extended objects with a stereotype of <<LogicalServer>> and <<LogicalServerPolicy>> respectively.
Multiplex Servers (IQ)
Sybase IQ v15.0 and higher supports multiplex, a highly scalable shared disk grid technology that allows concurrent data loads and queries via independent data processing nodes connected to a shared data source.
Login Policies (IQ/SQL Anywhere)
Sybase IQ (v15.0 and higher) and SQL Anywhere (v12 and higher) define the rules to be followed when establishing a user’s database connection in a database object called a login policy. PowerDesigner models login policies as extended objects with a stereotype of <<LoginPolicy>>.
LDAP Servers (IQ)
Sybase IQ v16 and higher supports delegating the authentication of users to LDAP servers. PowerDesigner models LDAP servers as extended objects with a stereotype of <<LDAPServer>>.
Remote Servers (IQ)
Sybase IQ v15.0 and higher supports remote servers, which define where remote objects mapped to a local proxy table are located. PowerDesigner models remote servers as extended objects with a stereotype of <<RemoteServer>>.
External Logins (IQ)
Sybase IQ v15.3 and higher supports external logins, which are alternate login names and passwords that are used when communicating with a remote server. PowerDesigner models external logins as extended objects with a stereotype of <<ExternLogin>>.
Spatial Data (IQ/SQL Anywhere)
Sybase IQ v15.4 and higher and SQL Anywhere v12 and higher can store spatial data (data that describes the position, shape, and orientation of objects in a defined space) using spatial reference systems.
Full Text Searches (IQ/SQL Anywhere)
Full text search can quickly find all instances of a term (word) in a database without having to scan table rows and without having to know which column a term is stored in. IQ (v15.2 and higher) and SQL Anywhere) support full text searches through text configurations and text indexes, which store complete positional information for every instance of every term in every indexed column.
Indexes (IQ)
Before creating IQ indexes, you should consider the implications of various types of indexes on the database server memory and disk space. The set of indexes you define for any given column can have dramatic impact on the speed of query processing.
Join Indexes (IQ/Oracle)
A join index is a special type of index, which represents a full outer join of two or more tables, where all rows from both tables are included in the result (with NULL returned for any column with no matching value). The query engine may use this full outer join as a starting point for queries that include left outer, right outer, and inner joins.
IQ Data Movement Scripts
PowerDesigner can generate data movement scripts to populate your AS IQ data warehouse from other databases. The script can generate a flat file for loading to the IQ data warehouse and create Insert Location statements for use with a proxy database (for ASE and ASA only).

Parent topic: DBMS Definition Reference