How To Change Old Records Into New Format Which Has New Attributes In Db2 Sql
— Predefined Object Types in Predict —
Defining Additional Attributes of Fields
If Additional attributes is ready to Y, a window is displayed containing additional attributes for pick.
Only those types of boosted attributes appear in the window that apply to the blazon of field. For example: the selection Adabas security & Edit mask is non contained in the list when a DB2 index field is processed.
More than 1 selection tin be fabricated at a fourth dimension. The respective input maps are then displayed i subsequently the other.
The additional attributes are described in the following sections:
-
3GL Specification
-
Condition Proper name and Value
-
Field Name Synonyms
-
One-time Mode Synonyms
-
Adabas Security and Edit mask
-
DBMS Extensions
-
Field Procedure
-
Derived Field Expression
-
Index Definition - DB2
-
Default value
-
Constraint name
-
Identity definition / Modify log
-
Platform Compatibilities
-
Base Extensions
3GL Specification
13:06:25 ***** P R Eastward D I C T 4.6.1 ****** 2007-05-31 - Modify Field - Field ID ........ HNO-EL1 Added 2007-05-31 at 12:55 File ID ......... HNO-FI1 by HNO Ty L Field ID F Cs Length Occ D U DB North NAT-l *- - -------------------------------- *- - -------- ----- * * -- * ----- i HNO-EL1 A ii.0 AA N Specifications for 3GL Gr.structur ........ (n) Justify ............ (R) Synchronized ....... (S) Initialize with ...* Init value ....... Indexed past ......... Depending on .......
| Attributes | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Gr.structure | The field attribute Gr.structure is used to change the record layout generated from a PE/PC field. If Gr.structure is set to N, all fields within a PE grouping are treated as multiple value fields. Setting Gr.construction to North prevents the format buffer for Adabas from becoming very large. Gr.structure = N tin only be specified for real fields in the deepest PE group (highest level number). For example: if there are 3 PE groups in the file on level 1, 4 and 6, only the PE groups on level half dozen can exist marked with Gr.structure = N. If Gr.structure is fix to blank, PE/PC groups are to exist generated as groups which occur n times as a whole. | ||||||||||||||||
| Justify |
| ||||||||||||||||
| Synchronized |
| ||||||||||||||||
| Initialize with |
| ||||||||||||||||
| Init. value | If Initialize with is either F or bare a value used for initialization of a field must/can be specified. Length and format of the Init value must be valid for the field. For binary fields hexadecimal constants such as FB0A are valid. See also Condition Name and Value. | ||||||||||||||||
| Indexed by | String that is used when generating the COBOL INDEXED By clause (only valid for fields of blazon MU/MC or PE/PC). | ||||||||||||||||
| Depending on | String used when generating the COBOL DEPENDING ON clause (only valid for fields of blazon MU/MC or PE/PC). | ||||||||||||||||
Condition Name and Value
13:04:26 ***** P R E D I C T 4.6.1 ****** 2007-05-31 - Modify Field - Field ID ........ VE-FIELD Modified 2007-05-31 at 12:21 File ID ......... HEB-A by HEB Ty L Field ID F Cs Length Occ D U DB Due north NAT-fifty *- - -------------------------------- *- - -------- ----- * * -- * ----- 1 VE-FIELD A iii.0 AA N Status name FC * Condition value Additional attributes ..* S Associations ..* Due north Scroll to .. 1
| Attributes | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Condition name | A value to be used when generating either equate data in Assembler copy code or a level 88 entry in COBOL copy code. Upward to 29,970 condition names can be entered. Each name needs at least one corresponding status value. Using condition names tin make logical conditions and assignments easier to handle. | ||||||||||||||||
| FC |
| ||||||||||||||||
| Status value | The length and format of this value must be valid for this field. This value must accept a corresponding condition name. Upwards to 29,970 condition values tin be entered. If several values represent to the same name, put the name before the first value and go out the proper noun field blank earlier later on values. THRU in the name field indicates a range of values catastrophe with the value on that line and beginning with the value on the previous line. | ||||||||||||||||
Field Name Synonyms
thirteen:48:12 ***** P R E D I C T 4.half-dozen.1 ****** 2007-05-31 - Modify Field - Field ID ........ PD-A2 Added 2007-05-31 at 12:x File ID ......... PD-A-TEST3 Modified 2007-05-31 at thirteen:08 Ty L Field name F Cs Length Occ D U DB N NAT-fifty *- - -------------------------------- *- - -------- ----- * * -- * ----- 2 PD-A2 A 2.0 AF Field proper name synonyms Natural ............. COBOL ............... PL/I ................ BAL/Assembler ....... FORTRAN ............. Pascal .............. Language ADA ........ Language C .......... User defined ........ Additional attributes ..* South Associations ..* N
| Attributes | |
|---|---|
| Field name synonyms | Synonyms to be assigned to the field when definitions in the following programing languages are generated: Natural, COBOL, PL/I, BAL (Assembler), FORTRAN, PASCAL, ADA or C. |
Old Mode Synonyms
This pick is only provided for compatibility with old versions of Predict.
Whether this option is displayed depends on the parameter Old fashion synonyms of the screen Defaults ->General defaults -> Synonyms:
| Northward | Default setting. Compatibility with former versions is not required. |
| Y | Up to 90 synonyms can exist divers as Natural synonyms. These create additional entries in the DDM which have the same attributes equally the original object but dissimilar names. |
thirteen:x:46 ***** P R E D I C T four.6.1 ****** 2007-05-31 - Modify Field - Field ID ........ PDS Modified 2007-05-31 at 13:01 File ID ......... PD-D1 by PD Field synonym Field synonym 1 2 3 4 v 6 7 8 9 10 eleven 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 xxx Additional attributes ..* Due south Associations ..* Due north Scroll to:
Adabas Security and Edit mask
13:48:35 ***** P R East D I C T 4.6.1 ****** 2007-05-31 - Modify Field - Field ID ........ PD-A2 Added 2007-05-31 at 12:01 File ID ......... PD-A-TEST3 Modified 2007-05-31 at xiii:08 Ty Fifty Field name F Cs Length Occ D U DB Northward NAT-fifty *- - -------------------------------- *- - -------- ----- * * -- * ----- 2 PD-A2 A two.0 AF Adabas attributes Edit mask .............. Security access level .. (0-15) Security update level .. (0-15) Additional attributes ..* S Associations ..* Northward
| Attributes | |
|---|---|
| Edit mask | The Adabas edit mask to be used for the field. Determines how numeric fields are to exist edited. Valid values: E1...E15. Edit mask is supported for compatibility reasons and for documentation purposes only. |
| Security access level | The Adabas admission security level of the field. |
| Security update level | The Adabas update security level of the field. |
DBMS Extensions
Applicable only to DB2 fields of blazon D or MT.
17:34:xl ***** P R East D I C T four.half-dozen.1 ***** 2007-05-31 - Change Field - Field ID ........ EL_1 Added 2007-05-31 at 17:34 File ID ......... HEB-DB2_ELS by HNO Ty 50 Field ID F Cs Length Occ D U DB N Df NAT-50 *- - -------------------------------- *- * -------- ----- * * -- * *- ----- 1 EL_1 A Due south iv.0 D AF U DBMS extensions in <Default Server> (new) Utilise equally security label .. (Y,Due north) Sectionalisation ..........* Nonpartitioning Cluster index..........* N (Y,North) Additional attributes ..* Due south Associations ..* Due north
| Attributes | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Use equally security label | Specifies that the tabular array column will contain security label values. A table tin can have only one security label column. To define a table with a security label column, the primary authorization ID of the argument must have a valid security label, and the RACF SECLABEL class must be active. In improver, the following weather are too required:
Refer to your DB2 documentation for further details. | ||||||||||||||
| Partitioning |
| ||||||||||||||
| Cluster index |
| ||||||||||||||
Field Procedure
16:41:49 ***** P R E D I C T 4.6.ane ****** 2007-05-31 - Add a Field - Field ID ........ HNO_TEST Added 2007-05-31 at 16:41 File ID ......... HNO-DB2 by HNO Ty Fifty Field ID F Cs Length Occ D U DB North NAT-l *- - -------------------------------- *- * -------- ----- * * -- * ----- one HNO_TEST AL 100.0 AF U Physical attributes in <Default Server> (new) +-------------------------------------------------+ ! Procedure name ....... ! ! Procedure parameter ! ! ! ! ! ! ! ! ! ! ! ! / ! +-------------------------------------------------+ Boosted attributes ..* S Associations ..* N
| Attributes | |
|---|---|
| Procedure name | Name of a field procedure (DB2 parameter FIELDPROC). See the Natural for DB2 documentation for more details. |
| Process parameter | Parameters passed to the field procedure. See the Natural for DB2 documentation for more than details. |
Derived Field Expression
> > + EL: A5 L: 1 S: 1 All ....+....1... Expression for derived field..+....5....+....half-dozen....+....7.. * * Field expression of a derived field * USER-TABLE1-SALARY * 12 /* SALARY FOR 12 MONTHS + CORRELATOR2-BONUS
Applicative simply to fields of type DV in files of the following types:
| B | Adabas SQL views |
| E | DB2 views |
| IV | Intermediate view |
| JV | Ingres view |
| OV | Oracle view |
| 10 | General SQL |
| Fifteen | Informix view |
| YV | Sybase view |
The expression used to derive the field is to be edited using one of the following depending on the your settings in the Profile > Handling screen:
-
the Natural-based Subquery Editor, or
-
the Software AG Editor
The editor can also be called with
-
function Edit Field expression (Code Y) in the Field Maintenance Carte, or
-
command
EDIT Element EXPRESSION file-id field-id
Run into the section Editors in Predict in the Predict Reference documentation for more information.
The subquery of the file that contains the current field can specify a correlation proper noun for any file whose fields it uses. The proper noun of each field referenced in the expression must be qualified (preceded) by the correlation proper name of the file from which the field is taken, if a correlation namehas been specified for that file, or the ID of the file from which the field is taken, if no correlation name has been specified for information technology. The expression can include both comment lines (with /*, * or ** in the first two columns) and line comments (preceded by /*).
Instance: A field which contains the almanac bacon:
* * Field expression of a derived field * USER-TABLE1-Bacon * 12 /* SALARY FOR 12 MONTHS + CORRELATOR2-BONUS
Index Definition - DB2
Index Default Definition / Defaults Values of Using- and Free-Block
Index fields (descriptor blazon D, F or P) in a file of blazon D (DB2 table), are defined in the screen below.
xi:28:09 ***** P R E D I C T 4.half-dozen.1 ***** 2006-04-20 - Modify Field - Field ID ........ INDEX Added 2006-04-xx at eleven:28 File ID ......... HNO-DB2 past HNO Alphabetize default definition in <Default Server> Index name ......... HNO-INDEX Number of partitions: 3 Cluster index ...... N (Y,N) Piece size ...* 0 Close option ....... Y (Y,N) Reverse scans . (Y,N) Bufferpool ........* Padded ........ (Y,N) Copy ............... N (Y,N) Compression ... (Y,N) UQ where non nil .* R non null Default values of using- and free-cake VSAM catalog proper noun .. Storagespace ......* SYSDEFLT Primary alloc ...... 1 Secondary alloc..... 2 Erase opt .......... (Y,N) Free pages ......... Per centum gratuitous .... GBPCACHE ..........* Default Additional attributes ..* S Associations ..* N
| Attributes | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| Index default definition | |||||||||
| Index name | The name of the DB2 alphabetize. Encounter Key or Index Fields in SQL Files - Superfields. A read-only field. | ||||||||
| Cluster index |
| ||||||||
| Bufferpool | The buffer pool associated with the index. | ||||||||
| Shut pick |
| ||||||||
| Copy |
| ||||||||
| Piece size | The maximum slice size for a non-partitioned index. Valid values: 0, 256, 512, 1024, 2048, 4096, 8192, 16384, 32768, 65536, 131072, 262144, 524288, 1048576, 2097152, 4194304. | ||||||||
| Contrary scans |
| ||||||||
| Padded |
| ||||||||
| UQ where not null |
| ||||||||
| Shrink |
| ||||||||
| Default Values of Using- and Free-Block | |||||||||
| VSAM catalog proper noun | Proper name of the VSAM itemize for the index. The parameter VSAM catalog name must be specified if data sets are already defined for the index. | ||||||||
| Storagespace | Storagespace where DB2 defines the information sets for the index (optional). If no storagespace is specified, DB2 uses the default storagespace. | ||||||||
| Chief alloc | Minimum principal space allocation (in Kbyte) for DB2-divers index data sets. A value specified in this field is stored only if the attribute storage group has been specified. | ||||||||
| Secondary alloc | Minimum secondary infinite allotment (in Kbyte) for DB2-defined alphabetize data sets. A value specified in this field is stored only if the attribute storage group has been specified. | ||||||||
| Erase opt |
| ||||||||
| Complimentary pages | A number from 0 to 255 which indicates that 1 page is to exist left free each time this number of pages is used when the load operation creates index entries or when the index is reorganized. Cipher indicates that no pages are to be left free. | ||||||||
| Pct complimentary | A number from 0 to 99: the percentage of each page to be left as free space when index entries are created by a load operation or when the index is reorganized. | ||||||||
| GBPCACHE |
| ||||||||
Partitioning definition
For a partitioned index (a cluster index for a table in a partitioned table space), the following screen is displayed for every 2 partitions. Each partition can so exist defined in accordance with the Default values of using- and free-cake (see clarification above).
fifteen:37:23 ***** P R East D I C T iv.six.ane ***** 2007-05-31 - Modify Field - Field ID ........ INDEX Modified 2007-05-31 at 15:37 File ID ......... HNO-DB2 past HNO SQL Server ...... <Default Server> ---------------------------- Partition definition ----------------------------- Segmentation one Value ..... 1 Zoom: North VSAM catalog name ..... Storagespace .........* HNO-SC Primary allocation .... GBPCACHE ........* Default Secondary allocation .. Costless pages ....... Erase selection .......... Due north (Y/North) Percent free .. Partition ii Value ..... 2 Zoom: Northward VSAM catalog name ..... Storagespace .........* HNO-SC-1 Primary resource allotment .... GBPCACHE ........* Default Secondary resource allotment .. Free pages ....... Erase option .......... Due north (Y/N) Percentage free .. * Additional attributes ..* Due south Associations ..* N Scroll to .. one
Note:
The data displayed in this screen depend on the values selected in the Partitioning field of the DBMS extensions screen.
| Attributes | |
|---|---|
| Value | The highest value of the index fundamental in this partition. At least one constant must exist used and as many constants as in that location are columns in the cardinal can be specified. The chain of all the constants is the highest value of the key in this partition of the index. Annotation: |
All other attributes are described above.
Default value
This additional attribute is only applicable for fields in
-
Sybase tables with Aught value option ready to R and Null default pick set to Y.
-
Adabas D tables, DB2 tables, Informix and Oracle tables with Null value option gear up to R or U and Cypher default selection set to Y.
xiii:09:33 ***** P R E D I C T 4.vi.i ****** 2007-05-31 - Change Field - Field ID ........ TESTFIELD Modified 2007-05-31 at 13:09 File ID ......... HNO-YT by HNO Ty L Field ID F Cs Length Occ D U DB N Df NAT-fifty *- - -------------------------------- *- * -------- ----- * * -- * *- ----- 1 TESTFIELD A B 10.0 AA R Y Default name ............. Default expression ......< >
| Attribute | |
|---|---|
| Default name | The default specified here is used in the CREATE TABLE statement. Sybase naming conventions use. Run into Naming Conventions for SQL Objects. Notation: |
| Default expression | An SQL expression can exist specified between the angled brackets. This expression determines the default value, for instance a constant or office. If specified, this value is e'er used by the office Generate CREATE argument. |
Constraint name
Depending on the field definition, up to four constraint names tin be specified.
xiii:35:45 ***** P R East D I C T four.six.1 ****** 2007-05-31 - Modify Field - Field ID ........ TESTFIELD Modified 2007-05-31 at 09:34 File ID ......... HNO-YT by HNO Ty L Field ID F Cs Length Occ D U DB N Df NAT-l *- - -------------------------------- *- * -------- ----- * * -- * *- ----- 1 TESTFIELD A B 10.0 P U AA R Y Attributes Constraint name Check constraint Principal key Unique Not zero
| Attributes | |
|---|---|
| Check constraint | Constraint name in the respective SQL organization for the fact that a linked verification of status S exists. |
| Primary cardinal | Constraint name for the fact that the field is a primary primal. |
| Unique | Constraint proper noun for the fact that a unique constraint exists (indicated with U in cavalcade Unique pick of the field object in Predict). |
| Null/Not goose egg | Constraint proper noun for the fact that the Null or Not zippo default option is set up to Y. |
Identity definition / Change log
These additional attributes are only applicable for fields in DB2 tables.
Identity definition
The following boosted attributes are simply applicable for fields of type
-
QN (Row ID) or
-
a numeric field.
15:56:16 ***** P R E D I C T four.6.one ***** 2009-05-31 - Modify Field - Field ID ........ HNO_AC Added 2009-05-31 at xv:56 File ID ......... HNO-DB2 by HNO Ty L Field ID F Cs Length Occ D U DB Northward Df NAT-l *- - -------------------------------- *- * -------- ----- * * -- * *- ----- QN 1 HNO_AC A 40.0 U AB R Identity ............. (Y,N) Identity definition in <Default Server> Generated .........* A Always Start value ........ Increment value .... Cache .............. Wheel .............. (Y,N) No minvalue ........ (Y,N) Min value .. No maxvalue ........ (Y,N) Max value .. Order .............. (Y,N) * Additional attributes ..* Southward Associations ..* N
| Attributes | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| Identity |
| ||||||||
| Generated |
| ||||||||
| Kickoff value | Specifies the first value for the identity cavalcade. | ||||||||
| Increment value | Specifies the interval betwixt consecutive values of the identity column. | ||||||||
| Cache | Specifies whether to proceed preallocated values in memory. | ||||||||
| Cycle | Specifies that values continue to exist generated for this column after the maximum or minimum value has been reached. | ||||||||
| Min value | Specifies the numeric abiding that is the minimum value that is generated for this identity column. | ||||||||
| No minvalue | Specifies whether or not a minimum end point of the range of values for the identity column has been set. | ||||||||
| Max value | Specifies the numeric constant that is the maximum value that is generated for this identity column. | ||||||||
| No maxvalue | Specifies whether or not a maximum end betoken of the range of values for the identity column has been set. | ||||||||
| Order |
| ||||||||
Modify log
The following additional attributes are only applicative for fields of type
-
TS (Timestamp)
14:xvi:twoscore ***** P R E D I C T 4.vi.1 ***** 2009-07-31 - Alter Field - Field ID ........ EL_TS Added 2009-02-13 at 08:56 File ID ......... HEB-D Modified 2009-06-04 at 14:xiv Ty L Field ID F Cs Length Occ D U DB Due north Df NAT-fifty *- - -------------------------------- *- * -------- ----- * * -- * *- ----- ane EL_TS TS AJ U Change log ............. N (Y,North) Change log definition in DADB29 Generated ...........* A Always * Boosted attributes ..* Southward Associations ..* N
| Attributes | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| Modify log |
| ||||||||
| Generated |
| ||||||||
Platform Compatibilities
The following topics are covered:
-
No Blank Compression
No Blank Compression
This additional aspect is just applicable for Adabas fields with format A, AV or LO. The option No Blank Compression controls that trailing blanks are suppressed when a value is stored.
sixteen:12:31 ***** P R East D I C T 4.6.1 ***** 2007-05-31 - Add together a Field - Field ID ........ HNO_A Added 2007-05-31 at xvi:11 File ID ......... HNO-ADA by HNO Ty 50 Field ID F Cs Length Occ D U DB N Df NAT-50 *- - -------------------------------- *- * -------- ----- * * -- * *- ----- 1 HNO_A A ten.0 AA Northward Platform compatibilities High society first ..... (Y) PF option ............ (Y) No Blank Compression . (Y) Boosted attributes ..* Southward Associations ..* N
Base Extensions
The post-obit topics are covered:
-
Fractional of Seconds
Fractional of Seconds
This additional attribute is only applicable for Oracle table fields with format TS. Enter a value from 0-9.
17:42:38 ***** P R E D I C T 4.6.1 ***** 2007-05-31 - Alter Field - Field ID ........ HNO_OT_TS Added 2007-05-31 at 17:41 File ID ......... HNO-OT by HNO Ty Fifty Field ID F Cs Length Occ D U DB N Df NAT-l *- - -------------------------------- *- * -------- ----- * * -- * *- ----- 1 HNO_OT_TS TS AA R Base of operations extensions Fractional of seconds .. Additional attributes ..* S Associations ..* N
Source: https://documentation.softwareag.com/natural/prd461/predef/elatt.htm
Posted by: smithfelich1959.blogspot.com
0 Response to "How To Change Old Records Into New Format Which Has New Attributes In Db2 Sql"
Post a Comment