This chapter describes the basic characteristics of the CZ schema, the schema settings and how they are used, and provides some schema maintenance tips.
Hey all, I ran into a little snag when writing a VBScript app for work that parses a text file and places that data into a DB2 V8.1 database via OLE DB ADO calls and a stored procedure with parameters. Some natural keys are totally manually generated. One of the most widely recognized use of a natural key is a stock ticker symbol - i.e. MSFT, APPL, and GOOGL. Natural keys serve as a great primary key when contextual meaning is important. A surrogate key is a key which does not have any contextual or business meaning.
This chapter covers the following topics:
Overview
This chapter describes the basic characteristics of the CZ schema, the schema settings and how they are used, and provides some schema maintenance tips:
Characteristics of the Oracle CZ Schema
For a description of the CZ schema, see Oracle CZ Schema.
Online Tables and Integration Tables
The CZ schema contains online and integration tables. The online and integration tables are organized into subschemas for storing the data of configuration models and saved configurations.
The online tables contain the data that is used by Oracle Configurator Developer and the runtime Oracle Configurator. Every online table that receives imported data has a corresponding import table. For example, CZ_ITEM_TYPES is populated with data from the CZ_IMP_ITEM_TYPE table during the import process. See CZ Subschemas for more information about the CZ subschemas.
The integration tables consist of import and control tables. See Import Tables for information about the import tables and Control Tables for information about control tables. See Populating the CZ Schema for information about using the integration tables.
CZ Subschemas
Both the online and integration tables of the CZ schema are organized into subschemas:
Additionally, there are some key table views:
See CZ Subschemas for a listing of tables in each subschema. For table details, see the CZ eTRM on MetaLink, Oracleâs technical support Web site.
Public Synonyms
The CZ schema does not use public synonyms.
Schema Customization
Customizing the data model of the CZ schema is not recommended, because such customizations may not be preserved during an upgrade or migration.
Various user expansion fields in the CZ schema, such as USERNUMn and USERSTRn in the CZ_PS_NODES table, are available for custom use. The data in the user expansion fields is preserved during a schema upgrade or migration. For more information, see the CZ eTRM on MetaLink, Oracleâs technical support Web site.
Import Tables
Every import table corresponds to an online table both structurally and relationally. Each import table contains the same fields as the corresponding online table, as well as additional fields to manage the import and correlate the data with the existing data in the online table.
Import tables consist of:
Because import tables are meant to capture as much data as possible, all fields are nullable and there are no integrity constraints such as primary-key definitions, unique indexes, or foreign-key references. The import tables allow batch population of the CZ schemaâs online tables.
Each import tableâs name is similar to its online counterpart. Import tables have CZ_IMP prefix instead of just CZ_. For example, the imported data in CZ_IMP_PROPERTY populates CZ_PROPERTIES, and CZ_IMP_ITEM_TYPE populates CZ_ITEM_TYPES.
The import tables temporarily store extracted or legacy data that concurrent programs access when creating, updating, or deleting records in the CZ schema. The CZ_IMP tables are populated by running the Populate or Refresh Configuration Models concurrent programs. For more information see Populate and Refresh Configuration Models Concurrent Programs.
For more information about:
Import Control Fields
Import control fields contain data that is used to manage the import process for each record. Import control data is not transferred to the online tables and is not used to resolve key values or anything else. Import Control Fields describes the import control fields.
The following table shows the Import Control Fields including field name, data type, and description.
Online Data Fields
The import tablesâ data fields exactly match the fields in the corresponding online table and are used to hold the data to be put into the online table.
Surrogate Key Fields
Surrogate key fields in the import tables hold the customer-provided extrinsic identifications for data to be imported. These include both foreign surrogate keys and surrogate primary keys.
Foreign Surrogate Key â A foreign surrogate key is a reference to a different table made through that tableâs surrogate primary key rather than through the online tableâs integer key value. A foreign surrogate key consists of one or more fields that resolve references from one import table to another. These keys are named FSK_table_refno_fldnum, where table is the name of the referenced table, refno is the number of the table-to-table reference, and fldnum is the position of the referenced surrogate-key field in the referenced import table. Note that refno is required to keep unique names for tables with multiple references to the same table, and generally, the fldnum is 1.
Surrogate Primary Key â As a rule, imported tables contain a single field named ORIG_SYS_REF, which is used to hold the external value that uniquely identifies each record. In some cases, however, the online CZ table has a primary key consisting entirely of references to other tables. In this case, the surrogate primary key actually consists of the foreign surrogate keys that correspond to the native foreign keys in the online table.
Dependencies Among Import Tables
Dependencies among import tables must be heeded especially when custom importing single tables. Dependencies Among CZ Schema Import Tables, 'Foreign Surrogate Key' lists the column in the import table whose value is dependent on the table listed in 'Depends on'. For example, the FSK_ITEMTYPE_1_1 column in CZ_IMP_ITEM_MASTER gets its value from CZ_IMP_ITEM_TYPE.NAME and helps in key resolution. FSK_ITEMTYPE_1_1 (default) is populated depending on the PK_USEEXPANSION indicator (0, 1, or 2) in CZ_XFR_TABLES. See Populating Import Tables for the order in which the CZ_IMP tables are populated.
Note: Oracle recommends that limited usage of FSK_***_EXT columns as these columns will eventually be desupported.
A strong dependency means a value is required to successfully import that record. If Default is YES, then there is a default value in that column and import succeeds even if the dependency is strong and no value is imported. The following Dependencies Among CZ Schema Import Tables lists the dependencies.
The following table shows the dependencies between the import tables.
Control Tables
The control tables provide the mechanism for controlling what data is imported or refreshed when populating the CZ schema import tables with data from outside sources. The control table names are prefixed with CZ_XFR.
When running Oracle Configurator Populate and Refresh Configuration Models Concurrent Programs, records in the CZ_XFR tables determine which import tables are enabled for import, what data is imported, and how the data is imported.
The following tables control the import process at the table and field level:
The following tables contain import information:
CZ_XFR_TABLES identifies the mapping of the import table to the online table, as well as the rules for importing data into the CZ schema.
CZ_XFR_FIELDS identifies the transfer rules for the fields that are transferred during the Populate or Refresh Configuration Models concurrent programs. Every field is updated during import or refresh, but the update can be retracted by using the NOUPDATE flag in the CZ_XFR_FIELDS table. If a field that is transferred does not have an entry in the CZ_XFR_FIELDS table, then that field is updated.
For example, setting the NOUPDATE flag to 1 in the CZ_XFR_FIELDS table for CZ_ITEM_MASTERS.DESC_TEXT, inhibits the updating of the Item Master description in CZ_ITEM_MASTERS.DESC_TEXT when a Model is refreshed. Setting a value in the CZ_XFR_FIELDS Table shows how to set the field in the CZ_XFR_FIELDS table so that changes made to the BOM Modelâs Item description do not appear in Oracle Configurator Developer.
Setting a value in the CZ_XFR_FIELDS Table
CZ_DB_SETTINGS Table
The CZ_DB_SETTINGS table provides parameters that affect certain applications and CZ schema processes.
Only one CZ_DB_SETTINGS table exists in a CZ schema.
Accessing the CZ_DB_SETTINGS Table
A userâs responsibility determines whether they can view or edit the CZ_DB_SETTINGS table. A user must have the Oracle Configurator Administrator responsibility to edit the CZ_DB_SETTINGS table through concurrent programs. For more information, see View Configurator Parameters and Modify Configurator Parameters.
Organization of the CZ_DB_SETTINGS Table
The parameters in the CZ_DB_SETTINGS table are mapped to a particular section of the CZ schema. The particular section is identified in the SECTION_NAME field and contains relevant database parameters. The sections are:
Each parameter contains the following fields:
CZ_DB_SETTINGS Parameters
Some of the CZ_DB_SETTINGS parameter values are predefined during an installation or upgrade of Oracle Configurator. The Oracle Configurator Administrator can modify the values of these parameters by running the Modify Configurator Parameters concurrent program. For information on running concurrent programs, see Running Configurator Concurrent Programs. For specific information on modifying the parameters in the CZ_DB_SETTINGS table, see Settings in CZ_DB_SETTINGS Table that lists the parameters in the CZ_DB_SETTINGS table that can be modified.
AltBatchValidateURL
AltBatchValidateURL allows the batch validation process to bypass the URL that is normally used for batch validation. This might be necessary if your database cannot communicate with your Web server.
If Oracle Configurator uses Secure Sockets Layer (SSL), then you can enable batch validation by creating an additional non-SSL-enabled (HTTP) servlet port and specifying its URL as the value of AltBatchValidateURL. For additional SSL information, see MetaLink, Oracleâs technical support Web site.
If your configurator servlet is set up to use HTTPS, then you can set AltBatchValidateURL to be a servlet using HTTP, and avoid some SSL encryption and handshaking overhead. Since the communication is between the Application database and the Application middle tier, communication does not cross the internet and thus HTTPS may not be necessary. This configuration does require extra setup and is not required.
You can also enable batch validation by using your existing SSL-enabled port for Oracle Configurator and setting up the Oracle Wallet for use by Oracle Configurator, as described on OracleMetaLink. In this case, you do not set any value for AltBatchValidateURL.
If you use a firewall, have your database setup in a DMZ, or have some other network configuration where the database cannot communicate with the web server, then you should set up an internal web server. After setting up an internal web server, you must then set the AltBatchValidateURL setting in the CZ_DB_SETTINGS table to be the URL for the configurator servlet on your internal web server.
For more information regarding DMZ setup, see MetaLink, Oracleâs technical support Web site.
To insert the AltBatchValidateURL into the CZ_DB_SETTINGS table, use the SQL INSERT statement shown in Adding AltBatchValidateURL to CZ_DB_SETTINGS.
Stored Proc With Recompile
Adding AltBatchValidateURL to CZ_DB_SETTINGS
BadItemPropertyValue
BadItemPropertyValue indicates the action that is taken when an Itemâs PROPERTY _VALUE in the CZ_IMP_ITEM_PROPERTY_VALUES table does not match the DATA_TYPE in the CZ_PROPERTIES online table. The default value (F) forces the record to be updated to include the PROPERTY_VALUE so that it is imported into the CZ_ITEM_PROPERTY_VALUES online table. Valid Values for the BadItemPropertyValue Setting lists the valid values for BadItemPropertyValue setting and the disposition:
The following table lists the valid values for the BadItemProperty value setting, along with a description of the value.
BatchSize
BatchSize indicates the number of records that are modified before committing a transaction in batch operations. The BatchSize setting is also used during a purge operation.
Ordinarily a database stored procedure runs as a single transaction that is considered pending until the calling operation commits the transaction. The pending changes are lined up in a rollback segment. If the calling operation is cancelled, then the transaction is rolled back. If the calling operation encounters an error, then the pending changes in the rollback segment are discarded. However, some batch operations, such as import, can involve many more records than the database can handle as a single transaction. If the transaction is too big, then the database fails an operation with a rollback-segment error. To avoid a rollback_segment error, import and other batch-like operations count up the modified records in the database and when the count matches the BatchSize value, the operation commits the transaction and resets the counter. Every record is not committed individually because it is considerably more economical to commit many updates at once.
BOM_REVISION
BOM_REVISION indicates the BOM revision in the Oracle Applications database from which data is being imported into the CZ schema. This setting is checked to ensure that the correct date format is used in the call to the BOM Model explosion procedure.
The value of BOM_REVISION is the Oracle Applications revision number used to determine which explosion date format to use. . Valid values are 5.0.628 for Release 10.7, 11.0.28 for Release 11.0, and 11.5.0 for both Release 11i and Release 12. If the value is null (default), then 11.5.0 is used. The call to the BOM Model explosion procedure checks up to the second decimal point of this value.
If the value is 11.5.n or Release 12, then the date format YYYY-MM-DD is used. Otherwise, DD/MON/RR is used for Release 10.7 or 11.0.
CommitSize
CommitSize indicates the number of import records in each database transaction between commits. CommitSize has the same purpose as BatchSize. for more information, see BatchSize. CommitSize is used during import.
DISPLAY_INSTANCE_NAME
DISPLAY_INSTANCE_NAME determines whether an Instance Name column appears in the Oracle Configurator Summary page. Oracle Configurator checks this setting only if multiple instances of one or more components exist in the configuration.
If DISPLAY_INSTANCE_NAME is set to TRUE and at least one component in the configuration has multiple instances, then the Instance Name column appears and displays the name of each instance.
If DISPLAY_INSTANCE_NAME is set to FALSE or there are no components with multiple instances in the configuration, then the Instance Name column does not appear. If set to False but there are multiple instances in the configuration, then instance names appear in the Description column (instead of each Itemâs description).
FREEZE_REVISION
FREEZE_REVISION indicates the revision number at the freeze stage. This parameter is used to capture the revision levels for the implementation of database package bodies and views. For example, if a table is tuned to improve performance, but the fields and the data returned are the same, then there is no need to change the MAJOR_VERSION or MINOR_VERSION but the FREEZE_REVISION value reflects the reworked view. This setting is read-only and populated when applying a patch.
GenerateGatedCombo
GenerateGatedCombo determines how a FALSE logic state is propagated in Explicit Compatibility, Property-based Compatibility and Design Chart Rules. See the Oracle Configurator Developer Userâs Guide for additional information about Gated Combinations.
GenerateUpdatedOnly
GenerateUpdatedOnly set to YES, causes logic generation to skip all referenced Models whose logic is up-to-date. GenerateUpdatedOnly set to NO causes the logic of all referenced Models to be generated even if their logic is up-to-date.
GenStatisticsBOM
GenStatisticsBOM set to YES forces the optimizer to update the internal statistics on the BOM_EXPLOSIONS table before running queries in the CZ schema. Generating statistics allows the optimizer to choose a better execution plan based on the current data structure in a table.
GenStatisticsCZ
GenStatisticsCZ set to YES forces the optimizer to update the internal statistics on the entire CZ schema before running queries in the CZ schema. Generating statistics allows the optimizer to choose a better execution plan based on the current data structure in a table.
MAJOR_VERSION
MAJOR_VERSION indicates the major version label for the CZ schema. This setting is read-only and is populated when upgrading the schema.
MaximumErrors
MaximumErrors indicates the limit of errors allowed before an import run is terminated. If you have a large amount of data to import, or you are not concerned with the process stopping once a certain number of errors is reached, then set this parameter to an extremely large number.
MemoryBulkSize
MemoryBulkSize regulates the memory usage of import. The smaller the setting, the less memory is required for import. This number is used during import for the cz_ps_nodes extraction procedure for specifying the number of records that are processed in the same pass. If the value entered is less than the total number of records to be imported, then the specified number of records is loaded and processed, and then the next group of records is loaded and processed. If there is no value entered, then the MemoryBulkSize is set to 10000000.
MINOR_VERSION
MINOR_VERSION indicates the minor version label for the CZ schema. This value is read-only and is populated when applying a patch. The MINOR_VERSION does not change during a particular family pack release.
MULTISESSION
MULTISESSION indicates the way in which a new import session interacts with other import sessions.
When MULTISESSION is missing from the CZ_DB_SETTINGS table, it is equivalent to the default 0.
If an import session is terminated, then the CZ_XFR_RUN_INFOS table may end up in an inconsistent state with the value of COMPLETED something other than 1.
OracleSequenceIncr
OracleSequenceIncr indicates the number of primary-key values allocated by each use of a sequence. The default setting means that keys are assigned in increments of 20. Both runtime Oracle Configurator and Configurator Developer ask for a sequence value once, and then manage the sequence value minus 1 in memory. When the block is used up, runtime Oracle Configurator and Configurator Developer again call for a sequence value. Keeping the default value at 20 saves round trips to the database.
Warning: Changing the default OracleSequenceIncr setting of 20 is likely to have adverse effects. The value of OracleSequenceIncr should not be modified.
PsNodeName
PsNodeName indicates the source field to be loaded into the NAME field in the CZ_PS_NODES table. The source field is either the RefPartNbr or the DESCRIPTION field in the MTL_SYSTEM_ITEMS table. RefPartNbr is the default so that the name loaded into the Model structure in Oracle Configurator Developer matches the name in CZ_ITEM_MASTERS.
PublicationLocalBOMSynch
PublicationLocalBOMSynch controls whether the BOM Synchronization process runs automatically when a publication is created for a Model that was imported from a remote server, on the same instance in which Configurator Developer is running. If BOM Synchronization does not run in this scenario, it is possible for the publication lookup process to fail if the host application is also running on the local instance that launched Oracle Configurator.
To automatically run BOM Synchronization in the scenario described above, set the CZ_DB_SETTINGS parameter PublicationLocalBOMSynch to YES by running the Modify Configurator Parameters concurrent program. Enter the following parameters when submitting this program:
PublicationLogging
PublicationLogging indicates whether a trace of the publication process is logged in the CZ_DB_LOGS table. The trace is helpful for debugging purposes and can be viewed in the log file. For more information about viewing log files, see Viewing Log Files.
PublishingCopyRules
PublishingCopyRules indicates whether or not configuration rules are copied during publishing. If PublishingCopyRules is set to NO, then only Configurator Extension rules are copied during publishing. The publishing process is faster when PublishingCopyRules is set to NO.
If the PublishingCopyRules is set to YES, then all rules are copied and both the source and published Models have the same rules.
Note: Setting 'PublishingCopyRules' to 'NO' only affects you if changes are made to logic generation that are incompatible with previous versions of Oracle Configurator. If the rules for a published Model are not copied, then you cannot generate logic for the published Models. Using the NO setting requires republishing all published Models.
PurgeDeleteConfigBatchsize
When you run the concurrent program Purge Configurator Tables, you can control its commit behavior by setting this parameter, which specifies how often the purge program issues a commit, in terms of a number of configurations. For example, a value of 200 specifies a commit after deleting a batch of 200 configurations.
RefPartNbr
RefPartNbr identifies the source fields that are loaded from the MTL_SYSTEM_ITEMS table into CZ_ITEM_MASTERS.REF_PART_NBR. This is a segment from the System Item key flexfield definition.
RefPartNbr determines what name is displayed for each imported Model structure node. The default value âCONCATENATED_SEGMENTSâ enables the BOM Model import process to construct BOM Model node names using multi-segment part numbers.
When RefPartNbr is set to âSEGMENT1â, only MTL_SYSTEM_ITEMS.SEGMENT1 is the source of the node names in the imported Model structure. If you want to use only the first segment of a part number as the node name, the Oracle Configurator Administrator must manually set RefPartNbr to âSEGMENT1â by running the Modify Configurator Parameters concurrent program.
Any value for RefPartNbr other than âCONCATENATED_SEGMENTSâ or âSEGMENT1â causes the import process to retrieve the value of the DESCRIPTION column from MTL_SYSTEM_ITEMS and displays the Item description as the node name in Configurator Developer.
Warning: Examine MTL_SYSTEM_ITEMS_VL. CONCATENATED_SEGMENTS to verify that the field is correctly populated. If the field is incorrectly populated, then the entry in Oracle Inventory may be wrong. If the entry is correct, check CZ_IMP_ITEM_MASTER.REF_PART_NBR to see that the value is the same as that in MTL_SYSTEM_ITEMS_VL. CONCATENATED_SEGMENTS.
Concatenated segments, including separators, must not exceed 1000 characters, which is the limit of the CZ_PS_NODES.NAME field. Any description longer than 1000 characters is truncated. The default separator is a dot (.). Other valid separators are |, -, or a custom value. See the Oracle Inventory Userâs Guide for more information about setting up part numbers.
You can enter multi-segment Items in the From Item and To Item input fields when you run either the Populate or Refresh Configuration Models concurrent program. You must include any separators that exist in the Itemâs part number when you enter multi-segment Item names.
Warning: When updating an existing Model in Configurator Developer to use multi- segment part numbers, you must either reimport or refresh the BOM Model. Confirm that the BOM Model is getting re-exploded during import. The CZ_DB_SETTINGS.RUN_BILL_EXPLODER should be Yes.
ResolvePropertyDataTypeStored Proc Sql
ResolvePropertyDataType controls whether Item Catalog Descriptive Elements are imported into Configurator Developer as Item Properties with a data type of Text or Decimal Number. If the value for this setting is NO, all imported Item Properties have a data type of Text in Configurator Developer.
If the value of this setting is YES, then all Descriptive Elements whose value is a number are imported as Item Properties and have a data type of Decimal Number. All Descriptive Elements whose value is text (for example, Weight) have a data type of Text.
If ResolvePropertyDataType is null, then all Descriptive Elements are imported into Configurator Developer as Item Properties with a data type of Text.
ResolvePropertyDataType Setting table illustrates how ResolvePropertyDataType affects how Descriptive Elements values are imported into Oracle Configurator Developer.
Item Property is a protected field in the CZ schema (the NOUPDATE flag is set during import). Once you import a BOM Model, you cannot change an Item Propertyâs data type simply by modifying the ResolvePropertyDataType setting and then refreshing the BOM Model.
RestoredConfigDefaultModelLookupDate
RestoredConfigDefaultModelLookupDate setting controls which publication Oracle Configurator uses on an order when called from Order Management. If this setting is config_creation_date, then Oracle Configurator uses the order line creation date. If this setting is null, then Oracle Configurator uses sysdate.
For more information, see DEFAULT_RESTORED_CFG_DATES.
Revision Date and User
Revision Date and User is read-only and documents the date and time at which the CZ schema was last upgraded, and the username of the user who performed the task.
RUN_BILL_EXPLODER
RUN_BILL_EXPLODER is a YES/NO flag (default=YES) that indicates whether the Oracle Applications Bills of Material exploder should be run on each bill that is marked for import in the CZ_XFR_PROJECT_BILLS table in the CZ schema at the time of import. See Populating the CZ Schema for more information on exploding a BOM Model.
The Oracle Configurator Populate or Refresh Configuration Models concurrent programs load bills and Items based on top bills listed in the CZ_XFR_PROJECT_BILLS table in the CZ schema. Before extracting, if the RUN_BILL_EXPLODER setting is set to YES, then the procedure calls the BOM Model exploder to refresh data in BOM_EXPLOSIONS for each record in the CZ_XFR_PROJECT_BILLS table. If RUN_BILL_EXPLODER is set to NO, then the concurrent program transfers the BOM Models that are flagged for import in the CZ_XFR_PROJECT_BILLS table without running the BOM Model exploder first.
Note: The Populate or Refresh Configuration Models concurrent programs do not explode BOM Models when importing from a remote server. See Exploding BOM Models in Oracle Applications for details.
CZ_INTL_TEXTS contains the text string from the DESCRIPTION field in the BOM_EXPLOSIONS table for each imported BOM Model structure node.
The Oracle Configurator SQL*Plus scripts and concurrent programs target all or a subset of BOM Models exploded in the BOM_EXPLOSIONS table in the Oracle Applications database. Selected BOM Model Items come from the BOM_BILL_OF_MATERIAL and the BOM_INVENTORY_COMPONENTS tables.
Note: Importing a BOM Model from a remote instance may fail if RUN_BILL_EXPLODER is set to YES in the local instance. (In this case, an error message similar to the following appears: 'ORA-03113: end-of-file on communication channel.') If this occurs, set RUN_BILL_EXPLODER flag to No and then re-submit the import concurrent program. The new setting should enable the process to complete successfully.
SuppressSuccessMessage
The SuppressSuccessMessage setting affects runtime Oracle Configurator behavior by suppressing messages that would normally be shown. The setting determines whether a message is displayed after fixing a validation error.
If SuppressSuccessMessage is set to NO, then after fixing a validation error a runtime success message is displayed. If SuppressSuccessMessage is set to YES, then after fixing a validation error a runtime success message is not displayed.
To insert SuppressSucessMessage into CZ_DB_SETTINGS, use the SQL*Plus INSERT statement shown in Adding SuppressSuccessMessage to CZ_DB_SETTINGS.
Adding SuppressSuccessMessage to CZ_DB_SETTINGS
TimeImportExec Stored Proc With Parameters
TimeImport enables the collection of timing information during import.
UI_NODE_NAME_CONCAT_CHARS
UI_NODE_NAME_CONCAT_CHARS sets the concatenation character that is used when generating UI captions using both the node name and description. The default concatenation character separating each text string is a comma surrounded by two spaces. (For example: 'AT62431 , Sentinal Custom Laptop'). The Oracle Configurator Administrator can change the concatenation character that separates each string by running the Modify Configurator Parameters concurrent program.
UseLocalTableInExtractionViews
UseLocalTableInExtractionViews is a YES/NO flag. If UseLocalTableInExtractionViews is set to YES, then definitions of some import extraction views include the DUAL table in the join. The UseLocalTableInExtractionViews setting is ignored if the import source server is local.
Note: If you are importing or refreshing from a remote database instance and the database instance is version 8i, then UseLocalTableInExtractionViews must be set to YES. This is because of an RDBMS bug. If this setting is not YES, then the following error appears in the cz_db_logs table after running the Populate and Refresh Configuration Models Concurrent Programs :'ORA-01025: UPI parameter out of range'
UtlHttpTransferTimeout
UtlHttpTransferTimeout allows modification of the timeout length that is used inside the call to the UTL_HTTP.REQUEST procedure during batch validation. The value is the number of seconds. Once the call completes, the timeout is set back to its original value.
To insert UtlHttpTransferTimeout into the CZ_DB_SETTINGS, use the SQL*Plus INSERT statement shown in Adding UtlHttpTransferTimeout to CZ_DB_SETTINGS.
Adding UtlHttpTransferTimeout to CZ_DB_SETTINGS
Note: This functionality is available only in Oracle 9i and later.
-->
APPLIES TO: SQL Server 2016 and later Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse
You can define a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. Creating a primary key automatically creates a corresponding unique clustered index, or a nonclustered index if specified as such.
Before You BeginLimitations and Restrictions
SecurityPermissions
Creating a new table with a primary key requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.
Creating a primary key in an existing table requires ALTER permission on the table.
![]() Using SQL Server Management StudioTo create a primary key
Caution
If you want to redefine the primary key, any relationships to the existing primary key must be deleted before the new primary key can be created. A message will warn you that existing relationships will be automatically deleted as part of this process.
A primary key column is identified by a primary key symbol in its row selector.
If a primary key consists of more than one column, duplicate values are allowed in one column, but each combination of values from all the columns in the primary key must be unique.
If you define a compound key, the order of columns in the primary key matches the order of columns as shown in the table. However, you can change the order of columns after the primary key is created. For more information, see Modify Primary Keys.
Using Transact-SQLTo create a primary key in an existing table
The following example creates a primary key on the column
TransactionID in the AdventureWorks database.
To create a primary key in a new table
The following example creates a table and defines a primary key on the column
TransactionID in the AdventureWorks database.
To create a primary key with clustered index in a new tableStored Proc For Generating Surrogate Keys Free
The following example creates a table and defines a primary key on the column
CustomerID and a clustered index on TransactionID in the AdventureWorks database.
See AlsoComments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2020
Categories |