Teradata C09-0001-07 User Manual Download Page 65

Page: 65 / 235

Summary of Contents for C09-0001-07

Page 1: ...User s Guide C09 0001 07 Release 3 0 DBCjl012 Data Base Computer...

Page 2: ...User s Guide C09 0001 07 Release 3 0...

Page 3: ...on All rights reserved No part of this document may be copied or reproduced in any form or by any means without the prior written consent of Teradata Teradata Corporation 12945 Jefferson Boulevard Los...

Page 4: ...1 1 through 1 8 07 2 1 through 2 2 07 3 1 through 3 30 07 4 1 through 4 8 07 5 1 through 5 20 07 6 1 through 6 38 07 7 1 through 7 20 07 8 1 through 8 6 07 9 1 through 9 10 07 10 1 through 10 12 07 1...

Page 5: ...quests DCRs Reflected in this Revision DCR 3101 DCR 2214 DCR 3167 DCR 2507 DCR 3785 DCR 2775 DCR 3578 DCR 2515 Discrepancy Reports DRs Reflected in this Revision DR 7729 DR 7789 DR 8202 DR 7811 DR 820...

Page 6: ...uide for that terminal before you read this guide ABOUT THIS DOCUMENT This document is the DBC 1012 Data Base Computer User s Guide Its purpose is to describe how to communicate with the DBC 1012 in o...

Page 7: ...ters 6 through 11 describe the use of the DBC SQL statements themselves Appendixes provide supplemental information This guide contains four appendixes Appendix A is a fold out copy of the Personnel d...

Page 8: ...oftware structure operating characteristics and configuration specifications DBC IOI2 Data Base Computer User s Guide document number C09 OOOI The user s guide is written for the non DP user The guide...

Page 9: ...d Data Dictionary Directory views the second card lists ITEQ and BTEQ commands and the third card lists COBOL and PL I Preprocessor statements DBC IOI2 Data Base Computer Operator s Guide document num...

Page 10: ...the DBC IOI2 system The manual presents the details of information flow data structures and the interface routines The manual covers CLIvI for hosts DBC IOI2 Data Base Computer Call Level Interface M...

Page 11: ...For each set of BTEQ commands it describes when and how to use them and how the commands are related It also describes each command in detail DBC lOl2 Data Base Computer CICS Interface Manual documen...

Page 12: ...l is written for the COBOL and PL I application programmer This manual presents details of preprocessor use and includes examples in COBOL and PL I DBC 1012 Data Base Computer Glossary document number...

Page 13: ...xii C09 OOOl 07...

Page 14: ...ing System Status Messages Interpreting the ITEQ Display Screen Entering Commands and Statements Ending an ITEQ Session EDITING DBC SQL STATEMENTS Entering a Statement from the Input Area Using PF Key...

Page 15: ...Using DBC SQL Report Writing Aids Defining Summaries WITH Clause Specifying Column Format Defining Headings and Summary Titles printing a Report CREATING A REPORT USING BTEQ SUMMARY AND PREVIEW QUERYI...

Page 16: ...REATING TABLES Specifying Column Attributes Specifying Data Type Specifying Default Control Specifying Case Specifying Format Specifying a Title Specifying Data Protection Providing for Fallback Data...

Page 17: ...ING A MACRO 9 10 9 9 SUMMARY AND PREVIEW 9 10 CHAPTER 10 SHARING DBC I012 FACILITIES 10 1 10 1 WHAT ARE PRIVILEGES 10 1 10 2 GRANTING PRIVILEGES 10 4 10 2 1 Granting Privileges to a User 10 6 10 2 2 G...

Page 18: ...TA DICTIONARY DIRECTORY VIEW FORMATS DEFAULT PF KEYS FOR ITEQ COMMANDS DEFINING ITEQ OUTPUT FILES DEFINING A PRINT OUTPUT FILE DEFINING A RESULT OUTPUT FILE ILLUSTRATIONS Communicating with the DBC 10...

Page 19: ...Assignments Display Commands 3 23 BTEQ Commands 2 parts 4 2 ITEQ Format Commands 5 3 Format Characters 5 9 BTEQ Formatting Commands 2 parts 5 15 Comparison Operations 6 5 Logical Operations 6 8 Expres...

Page 20: ...pplication program that contains requests and instructions for the DBC I012 Data Base Computer embedded directly in the source code The preprocessors are described in the DBC I012 Data Base Computer P...

Page 21: ...LINE BATCH TRANSACTION APPLICATION SYSTEM PROGRAM Teradata 1 TOP INTERACTIVE SUBSYSTEM r r _ I I I ITEQ I L _ _ 1 1tBLOCK MULTIPLEXER ICHANNEL B Teradata V OBC 1012 DIRECT INTERACTIVE ENVIRONMENT Fig...

Page 22: ...ts is described in Chapters 6 through 10 of this guide For more detailed information on all DBC SQL statements refer to the DBC 1012 Data Base Computer Reference Manual 1 1 HOW A DBC 1012 DATA BASE IS...

Page 23: ...table The intersection of a column and a row is called a field For example the fourth row of the Mobile Homes table has three fields The data in its Model_Name field Is Del Fuego Figure 1 3 shows two...

Page 24: ...8 000 00 11 55 11 27 F A H 16 0 0 t1 10012 Watson L 500 Vice Pres 56 000 00 8 43 10 03 H c S 20 0 1001 Regan R 600 Purchaser 44 000 00 10 48 10 20 F C H 16 0 to3 PI 10014 Inglls C 500 Tech Writer 34 0...

Page 25: ...the statement and ITEQ displays he result on the terminal screen ITEQ allows you to 6 Enter Edit and Execute DBC SQL Statements You can enter and execute DBC SQL statements from the terminal If the re...

Page 26: ...e DBC SQL statements to the DBC 1012 for processing in batch mode BTEQ commands included with the DBC SQL statements provide for session control formatting of DBC SQL results and handling of output da...

Page 27: ...1 8 C09 OOOl 07...

Page 28: ...kept secret to prevent another from accessing data under your username In some cases you may also need to obtain An account number This identifier is associated with your username and is used for acc...

Page 29: ...the ITEQ FILE command during an ITEQ session to save the result of the current SELECT or EXECUTE MACRO statement a host flat file is created with a logical record length of 30004 bytes Under TSO a dat...

Page 30: ...ed on to your organization s host computer you may start ITEQ log on to the DBC I012 and begin entering DBC SQL statements and ITEQ commands 3 1 1 Starting ITEQ If the output files needed for your ITE...

Page 31: ...ement before executing the ITEQ LOGON command described in the next section ITEQ will display a request to please logon Warning while the ITEQ screen is displayed do not press the following keys on yo...

Page 32: ...p of the screen If a STARTUP string is defined for you using a STARTUP clause in the CREATE USER or MODIFY USER statements Chapter 10 the processing result is displayed following this mes age The stat...

Page 33: ...ant to check with your System Administrator to see if this logon option is available You may start ITEQ and log on to the DBC 1012 by keying only one command for example Under TSO ITEQ LOG 4 0mura H d...

Page 34: ...ESS ITEQ is processing a command or has sent a statement to the DBC 1012 system and is waiting for a response DATA AVAILABLE More data from the last data generating READY FOR COMMAND command or statem...

Page 35: ...tus Area 1 line 24 Figure 3 2 ITEQ Display Screen The display area normally is used to display responses to ITEQ commands and DBC SQL statements However you can also use this area to compose a lengthy...

Page 36: ...ecuted in the host computer When executed the command is erased from the input area Depending on the command there may also be some visible change on the terminal screen to indicate execution To enter...

Page 37: ...o IN 100 500 ORDER BY DeptNo Name END OF DATA READY FOR COMMAND Figure 3 3 Result of a SELECT Statement 3 1 6 Ending an ITEQ Session To end an ITEQ session key the command LOGOFF and press ENTER ITEQ...

Page 38: ...and editing a long DBC SQL statement or macro from the display area may be more convenient because it enables you to view the statement in its entirety Also a statement or macro that is entered in th...

Page 39: ...t line overlaying the cursor and erasing any characters to the right of the cursor You must assign a PF key to this command as described below REMOVE Removes the line on which the cursor is positioned...

Page 40: ...re during processing and display of its processing message and its result If an error message or the result tells you that the statement is in error you may edit the command or statement without rekey...

Page 41: ...your own PF key assignments To assign a PF key to an ITEQ command use the SET PFn ITEQ command For example to assign the PF23 key to the UP edit command key SET PF23 UP 1 in the input area and press E...

Page 42: ...statement discussed in Chapter 9 is needed to convey the command to ITEQ The entire STARTUP string is enclosed by apostrophes Each SET PFn command is identified within the string by double apostrophes...

Page 43: ...tNo IN 100 500 600 WITH SUM Salary TITLE TOTAL BY DeptNo ORDER BY Name Use the ollowing procedure to enter this statement 1 Erase the input area by pressing the PF17 key assigned to the CLEAR INPUT ed...

Page 44: ...P edit command until the second line appears 2 Move the cursor to the incorrect word 3 Key the correct spelling over it 4 Press the PF24 key assigned to the DOWN edit command to return to the line tha...

Page 45: ...nal keyboard 3 Key the characters 4 Press the RESET key to cancel insert mode To de1ete characters in a statement line 1 position the cursor on the first character to be deleted 2 Press the DELETE key...

Page 46: ...ASE EOF erases a line from the cursor position to the end of the line When you enter a new statement the display area is cleared 3 2 2 Editing a Statement in the Display Area The display area is used...

Page 47: ...appears in the display area may be printed using the PRINT command described in Chapter 5 and Appendix D 3 2 3 Aborting a Statement If you want to terminate processing of a DBC SQL statement after the...

Page 48: ...up string as described above 3 2 3 1 Aborting a Statement Under TSO You may abort processing of the current DBC SQL statement as described in Table 3 5 Interrupt ITEQ 1 Enter the ABORT command 2 The s...

Page 49: ...s 3110 The transaction was aborted by the user Returns normal processing result Processes new statement or command Exits ITEQ normally displays READY Interrupts terminates ITEQ Aborting a Statement Un...

Page 50: ...tem Response Begins processing statement Interrupts ITEQ dis plays ITEQ ATTENTION HANDLING Attempts to abort processing of current statement Displays 3110 The transaction was aborted by the user Retur...

Page 51: ...Commands ITEQ provides display commands that let you view result pages These commands are listed in Table 3 7 Any abbreviation allowed in keying a command is indicated in parentheses following the com...

Page 52: ...ng the statement completely by executing the SHOW command assigned to the PF13 key The statement is redisplayed in the display area where it can be modified and entered using the SUBMIT command In gen...

Page 53: ...umn headings are displayed as a single entity If the result exceeds the size of the display area you may view the result as consecutive screen pages without column headings or other embellishment Unfo...

Page 54: ...09 Marston A 500 Secretary 22 000 00 8 53 0 10010 Reed C 500 Technician 30 000 00 4 49 0 10011 Chin M 100 Accountant 38 000 00 11 55 1 10012 Watson L 500 Vice Pres 56 000 00 8 43 1 10013 Regan R 600 P...

Page 55: ...esult Regardless of the length of a result you may press PF20 to display each consecutive page until you reach the last page indicated by END OF DATA READY FOR COMMAND At this point pressing PF20 has...

Page 56: ...e warns you that a report based on this result will not fit on B 5 by II inch paper which has an BO character maximum line width Note that the message also alerts you to the fact that more data is ava...

Page 57: ...LE READY FOR COMMAND Note that the processing message remains displayed because it is formatted to the size of the display area Press PF23 once more the screen shifts right until only the HCap column...

Page 58: ...e Before entering a subsequent query you may execute the command FILE to store the spooled result of the current SELECT statement or macro When control is returned to the interactive system this file...

Page 59: ...3 30 C09 0001 07...

Page 60: ...eat a request Use of more than one session for improved performance Ability to perform conditional tests and branching In addition BTEQ provides sophisticated report writing commands that allow you to...

Page 61: ...nds refer to the DBC 1012 Data Base Computer Reference Manual Table 4 1 BTEQ Commands 1 of 2 Activity Command Function Session LOGOFF Terminates a DBC 1012 Control session without exiting BTEQ LOGON Q...

Page 62: ...ment Limits BTEQ output to reporting only errors and request processing statistics Enables the user to include up to three lines of commentary Causes BTEQ to repeat the next DBC SQL request a specifie...

Page 63: ...setname is the name of the load library that contains the BTEQ program ITBMAIN You may pass a parameter to BTEQ by entering CALL datasetname ITBMAIN TDP TDPO In this example the BTEQ TDP command is us...

Page 64: ...the beginning and the end of the data set that contains the script of DBC sQL statements and BTEQ commands that defines the job RUNNING BTEQ UNDER VM CMS You may invoke BTEQ on line by entering BTEQ...

Page 65: ...DISK filename filetype filemode Identifies the input file that contains the BTEQ input stream the script of DBC SQL statements and BTEQ commands EXTRACTING DBC IOI2 DATA TO A HOST DATA SET The exampl...

Page 66: ...D DSN CTI SAVEDATA TEXT DISP NEW CATLG II UNIT SYSDA SPACE TRK l l RLSE II DCB LRECL 80 RECFM FB BLKSIZE 800 IISYSIN DD DATA DLM IMPORT DATA DDNAME INFILE EXPORT DATA DDNAME SAVEDATA SESSIONS 2 LOGON...

Page 67: ...EQ to open two sessions to process the job LOGON Logs the user on to the number of DBC 1012 sessions specified by SESSIONS REPEAT Causes BTEQ to repeat the SELECT request 10 times EXPORT RESET Cancels...

Page 68: ...the processing message is displayed by itself as the first page of a result Result data is formatted on consecutive pages each containing date page number report title and column headings Formatted pa...

Page 69: ...umber Blanks in place of null values For the sake of illustration in this example we have shown a null value see Table 7 2 in the salary column for Greene Non suppressed repeating values for example i...

Page 70: ...FORMAT ON Sets Format mode SFO SET FORMAT OFF Sets Unforrnat mode SFF SET NULL AS string SNA SET PAGELENGTH n SET RTITLE string SET SUPPRESS OFF ALL n n SET SUPPRESS ON ALL n n SET WIDTH n C09 OOOl 0...

Page 71: ...er 5 1 2 2 Viewing the Effect of Format Commands You normally apply ITEQ format commands only to a result displayed in Format mode However you may apply the following format commands to a result displ...

Page 72: ...the example result above Note the use of the double slash II character to break the title string into two lines A title may be broken into up to three lines A report title may be up to 254 characters...

Page 73: ...reene W Jones M 50 000 00 Moffit H 35 000 00 Peterson J 25 000 00 700 Smith T 45 000 00 Note that a blank line is inserted before a value change in a column under SUPPRESS control This feature may be...

Page 74: ...H clause allows you to specify group subtotals For example to display salary subtotals for each department add another WITH clause to the statement as follows SELECT DeptNo Name Salary FROM Employee W...

Page 75: ...e WHERE DeptNo IN 100 700 WITH SUM Salary BY DeptNo DESC WITH SUM Salary ORDER BY Name Specifying Column Format You may change the format defined in the CREATE TABLE statement of data displayed in a r...

Page 76: ...a in the column For example in the statement SELECT Name Salary FORMAT 9 FROM Employee WHERE DeptNo 600 AND Sa1ary 12 2500 1 the phrase FORMAT 9 eliminates the comma and decimal places defined for the...

Page 77: ...sult is positive or negative A character translates to a blank for a positive result or to a for a negative result A dollar sign character A single character used in a FORMAT phrase places the charact...

Page 78: ...the column headings of a result originally defined when the table was created as well as the titles of summary results using the DBC SQL TITLE phrase Use TITLE in your report statement to provide mor...

Page 79: ...e report on narrow printer paper 80 characters by 55 lines set the line width and page length specifications by executing the following format commands WIDTH 80 PAGELENGTH 55 The WIDTH command specifi...

Page 80: ...use your report to be printed when control is returned to the interactive system execute the PRINT command PRINT The report is printed according to system defaults determined by your DBC 1012 installa...

Page 81: ...more comprehensive formatting capabilities than does ITEQ These include Flexibility in defining report titles and headings Ability to print a footing at the bottom of each report page Ability to speci...

Page 82: ...the formatting mode for displaying and printing selected results SET HEADING Sets a heading for a report that appears at the top of each page SET NULL Specifies a character or string that is used to r...

Page 83: ...ies whether a line of dash characters is printed immediately preceding a summary title as shown in the example report SET TRANSLATE Specifies whether data is trans lated from ASCII to EBCDIC before it...

Page 84: ...sition Salary YrsExp TITLE Yearsl Experience FROM Department Employee WHERE Loc IN NYC ATL AND Salary 15000 AND Department DeptNo Emp1oyee DeptNo ORDER BY Loc Department DeptNo Name WITH SUM Sa1ary TI...

Page 85: ...YC 100 Chin M Controller 38 000 00 11 Greene W Payroll Ck 32 000 00 15 Jones M Vice Pres 50 000 00 13 Moffit H Recruiter 35 000 00 3 Peterson J Payroll Ck 25 000 00 _ Total for Department 100 180 500...

Page 86: ...to up to three separate sections the first left justified the second centered and the third right justified A BTEQ SET RTITLEcommand similar to the ITEQ SET RTITLE command is also available for report...

Page 87: ...s chapter showed you how to format a report using ITEQ or BTEQ commands and DBC SQL features The next chapter shows you how to compose a DBC SQL SELECT statement so that you can query data stored on t...

Page 88: ...For example in SELECT DeptNo Name Salary FROM Personnel Employee WHERE DeptNo IN 100 500 ORDER BY DeptNo Name SELECT is the statement keyword DeptNo Name and Salary are column names Personnel is the d...

Page 89: ...ABLISHING A DEFAULT DATA BASE During a session with the DBC I012 you may repeatedly query define or manipulate data from the same data base To avoid keying the data base name every time you enter a DB...

Page 90: ...tatement is described in Chapter 10 Henceforth the statement examples in this guide assume that you have established Personnel as your default data base 6 3 SELECTING COLUMNS To get data from a data b...

Page 91: ...0 Payroll Ck Note that the left to right order of the columns in a result is determined by the order in which the column names are entered in the statement Note also that as long as a statement contai...

Page 92: ...arch condition in this WHERE clause The comparison operators listed in Table 6 1 are routinely used to specify WHERE search conditions Form Table 6 1 Comparison Operations Meaning value1 va1ue2 value1...

Page 93: ...ORDER BY clause of the SELECT statement For example to list the name and years of experience of each employee in Department 600 in ascending order of seniority enter SELECT Name YrsExp FROM Employee...

Page 94: ...CT keyword allows you to specify that no two entries in a result be alike For example if you wanted a listing of employee titles for Department 500 the following statement SELECT JobTitle FROM Employe...

Page 95: ...WHERE condition OR condition OR WHERE condition AND NOT condi tion Specifies a number of conditions that must be satisfied Specifies a number of conditions one of which must be satisfied Specifies a c...

Page 96: ...P 28 600 00 M F Chin M 38 000 00 M F Watson L 56 000 00 S M Marston A 22 000 00 M F Moffit H 35 000 00 W F Inglis C 34 000 00 S M Leidner P 23 000 00 M F Smith T 45 000 00 S F Brangle B 30 000 00 S F...

Page 97: ...by default the NOT operator is evaluated first in a statement then AND then OR the statement above would have the same result without the parentheses However this is not always true Parentheses are o...

Page 98: ...atches row data Against each member of a directly specified set of values or character combinations Against a set of values or character combinations specified as the result of a SELECT statement The...

Page 99: ...f this statement is identical to that of the statement above SELECT Name Salary DeptNo FROM Employee WHERE DeptNo 100 OR DeptNo 500 AND MStat NOT M To select rows of the Employee table in which depart...

Page 100: ...ll within a certain range use the BETWEEN AND comparison operator in the WHERE clause of the SELECT statement For example to get a list of names salaries and titles for employees in Department 500 who...

Page 101: ...represents any string of zero or more characters The _ character represents any single character The following examples illustrate uses of the LIKE partial string operator For example to get a listing...

Page 102: ...select rows that satisfy a calculated condition by including arithmetic operators in the WHERE clause of the SELECT statement Arithmetic operators are listed in Table 6 4 Table 6 4 Operator MOD Arithm...

Page 103: ...LL The result of this query is the names of all employees with a null value in the DeptNo field Because all employees contained in the Employee table have been assigned to a department no rows will be...

Page 104: ...wo or more SELECT statements Returns result rows that are in all of answer sets generated by individual SELECT statements Subtracts the result rows generated by the second SELECT statement from the re...

Page 105: ...A set operator cannot be used in a subquery A set operator cannot be used in a view definition 6 4 11 1 UNION Operator You may use the UNION operator to combine the results of two or more SELECT stat...

Page 106: ...data from two different tables and merge that data into a single list SELECT Name DeptNo FROM Employee_Dept_500 UNION SELECT Name DeptNo FROM Employee_Dept_600 6 4 11 2 INTERSECT Operator The INTERSEC...

Page 107: ...xample if the following two tables are used Table SPart Table SLocation Column SuppNo PartNo Column SuppNo SuppLoc Row 100 P2 Row 100 London 101 PI 101 London 102 PI 102 Toronto 103 P2 103 Tokyo then...

Page 108: ...ne the suppliers in London who do not supply part PI 6 5 SELECT SuppNo FROM SLocation WHERE SuppLoc London MINUS SELECT SPart SuppNo FROM SPart SLocation WHERE SPart PartNo PI AND SPart SuppNo SLocati...

Page 109: ...eration YrsExp 200 would be divided by 200 and the result added to Salary producing an erroneous result Note the use of the NAMED phrase in this statement to associate the arithmetic expression Salary...

Page 110: ...lOO OO 22 000 00 65 000 00 In this statement SUM totals all values in the salary column MIN locates the minimum salary and MAX locates the maximum salary You may perform an arithmetic operation on th...

Page 111: ...n the unique occurrences of an expression For example as a prefix operator in the following statement SELECT COUNT Sex FROM Employee WHERE Sex F COUNT provides a total of women Employees Count Sex 9 I...

Page 112: ...ave non null department numbers Again the two new employees are not included in the count DeptNo Count DeptNo 100 4 300 3 500 7 600 4 700 3 To include the new employees in a count by department use CO...

Page 113: ...the data type for the DOB Date of Birth column is defined as DATE The following kinds of operations may be performed on data that has the DATE data type Arithmetic addition subtraction and division Co...

Page 114: ...1938 and August 25 1942 the following query could be entered SELECT Name DOB FROM Employee WHERE DOB BETWEEN 380307 DATE AND 420825 DATE ORDER BY DOB Name DOB Inglis C Mar 07 1938 Peterson J Mar 27 1...

Page 115: ...atement as follows SELECT Name DaB INTEGER FROM Employee WHERE DOB BETWEEN 380307 DATE AND 420825 DATE ORDER BY DaB The display is changed as follows Name DOB Inglis C 380307 Peterson J 420327 6 7 CHA...

Page 116: ...2 Kemper R 10006 Inglis C 10014 Leidner P 10003 Smith T 10021 Carter J 10016 Phan A 10018 Regan R 10013 Greene W 10017 Marston A 10009 Moffit H 10002 Reed C 10010 Omura H 10015 Brangle B 10020 Peterso...

Page 117: ...been suppressed Now when the TRIM function is removed the statement SELECT Last Name I I I I First Name FROM Names returns in the string Jones Mary In this example the trailing blanks are not suppres...

Page 118: ...The example below illustrates use of the INDEX string function SELECT Name FROM Employee WHERE INDEX Name 6 The DBC 1012 returns all employee names in which the last name is 6 characters or longer 6...

Page 119: ...the Salary column by department number The aggregate operators AVG MIN and MAX are then applied to each group 6 8 1 Selecting Specific Groups To further refine selected data you may specify that each...

Page 120: ...clauses ORDER BY WITH BY Note that when non aggregate groups for example DeptNo in the previous example are selected along with aggregate groups the non aggregate groups are always included in the GRO...

Page 121: ...es a relationship between rows in different tables or views You may also want to establish a relationship between different rows in the same table or view To do this you treat the table or view as two...

Page 122: ...00 Watson L 8 Omura H 8 500 Watson L 8 Carter J 20 500 Watson L 8 Newman P 6 600 Aguilar J 11 Aguilar J 11 600 Aguilar J 11 Regan R 10 600 Aguilar J 11 Kemper R 7 600 Aguilar J 11 Clements D 9 700 Smi...

Page 123: ...parameter for the WHERE clause of the third query Name SELECT Name FROM Employee WHERE EmpNo 10012 Watson L Using the subquery feature of DBC SQL you may obtain the final result of these three queries...

Page 124: ...ELECT statement places a read lock on the table or tables or for a prime key query on one or more rows of these tables that contain the data that it is querying If the SELECT statement places a read l...

Page 125: ...SELECT statement to obtain data from DBC 1012 data bases The next chapter shows you how to use DBC SQL data definition statements to Create modify rename and drop tables Create and drop indexes Creat...

Page 126: ...ing the following DBC SQL data definition statements CREATE ALTER DROP RENAME TABLE CREATE DROP RENAME REPLACE VIEW CREATE DROP RENAME REPLACE MACRO CREATE DROP INDEX CREATE MODIFY DROP USER DATABASE...

Page 127: ...WEEN 1 00 AND 999000 00 YrsExp BYTEINT FORMAT Z9 BETWEEN 99 AND 99 DOB DATE FORMAT MMMBDDbYYYY NOT NULL Sex CHAR l UPPERCASE NOT NULL Race CHAR l UPPERCASE MStat CHAR l UPPERCASE EdLev BYTEINT FORMAT...

Page 128: ...for null values Case A specification of how character data is to be stored and displayed Range An upper and lower limit on numeric data that is stored Format A specification of how numeric data is to...

Page 129: ...ned binary whole number BYTE n A fixed length binary string of n bytes A byte is an a bit element of data that is stored on the DBC lOl2 without being translated into an internal representation SMALLI...

Page 130: ...te notation for example Format Display DDBMMMBYYYY 03 Jul 1984 YY MM DD 84 07 03 MM DD YY 07 03 84 MMMBDD YY Ju103 84 DD MM YYYY 03 07 1984 In addition a day within year format using DDD for day with...

Page 131: ...or occurs If a null or an expression that evaluates to null is inserted in a numeric field for which a DEFAULT value has been specified a null is inserted instead of the value unless NOT NULL has been...

Page 132: ...phrase discussed in Chapter 5 For example the specification for the Name column in the CREATE TABLE statement above might be entered as Name CHAR 12 NOT NULL TITLE EmployeeIIName The phrase TITLE Empl...

Page 133: ...K is specified you must specify the FALLBACK option in your CREATE TABLE statement Conversely if you do not want to create a fallback copy of a new table in a data base or user space for which FALLBAC...

Page 134: ...a high percentage of compressed values or if the column is type BYTEINT SMALLINT or CHAR n if n is a small number 7 1 4 Establishing Indexes The CREATE TABLE statement lets you establish indexes duri...

Page 135: ...such as joins However such an advantage is not achieved without a tradeoff Every secondary index defined for a table requires a secondary index subtable Secondary index subtables take up extra space o...

Page 136: ...ion from the Employee table enter the following two statements in sequence CREATE TABLE Manager NO FALLBACK EmpNo INTEGER FORMAT ZZZZ9 Name VARCHAR 12 NOT NULL UNIQUE PRIMARY INDEX EmpNo INSERT INTO M...

Page 137: ...mpMonthSal 7 3 ALTERING A TABLE DEFINITION After a table is created you may change its definition in the following ways 1 Add or drop one or more columns from the table 2 Change the default control fo...

Page 138: ...g statement ALTER TABLE Employee DROP HCap removes the HCap column from the Employee table To enter an ALTER TABLE statement for a table you must have the drop privilege for the table 7 3 2 Changing A...

Page 139: ...To add a single copy of an after image journal enter ALTER TABLE NewEmployee AFTER JOURNAL 7 3 5 Changing the Data Type Attribute To change the data type attribute for one or more columns of a table u...

Page 140: ...W TABLE Employee sets the display area for input and displays a synthesized CREATE TABLE statement for the Employee table When the CREATE TABLE statement is displayed you may change the statement as d...

Page 141: ...strain the values that are inserted or updated in the underlying table Enable other users to insert update or delete specified data in a table as described in Chapter 8 Though it may display the same...

Page 142: ...ement specifies a join between Employee and Department tables The WHERE clause specifies the condition for the join on DeptNo and excludes information for vice presidents and managers Different names...

Page 143: ...tatement to allow users to concurrently update and query the same table In this instance an ACCESS lock which allows users to query a table that has been locked for write access is specified in a CREA...

Page 144: ...view in the display area where you may change it using ITEQ edit commands 7 5 DOCUMENTING TABLES COLUMNS VIEWS After creating a table or a view you may define and store a descriptive comment about th...

Page 145: ...view you must qualify its name for example COMMENT ON Personnel Employee 7 6 RENAMING TABLES AND VIEWS You may rename an existing table or view using the RENAME statement For example to change the na...

Page 146: ...ABLE Employee To remove the Emplist view enter DROP VIEW Emplist You must have the drop privilege on the table or view to remove it 7 8 SUMMARY AND PREVIEW This chapter showed you how to work with tab...

Page 147: ...7 22 C09 OOOl 07...

Page 148: ...following ways By directly specifying the row data to be inserted By retrieving from another table the row data to be inserted Data inserted in a table must conform to the following constraints In gen...

Page 149: ...d 8 1 2 Inserting Data by Query You may insert rows in a table by selecting rows from another table or a view For example assume that you have created a table named Promotion to identify employees who...

Page 150: ...employee Leidner enter UPDATE Employee SET JobTitle Secretary Salary 23000 YrsExp 13 Race C MStat M EdLev 16 HCap O WHERE EmpNo 10003i You use a WHERE clause to specify one or more rows to which data...

Page 151: ...separate data base these employees could be deleted from the Employee table as follows 8 4 DELETE FROM Employee WHERE Employee DeptNo Department DeptNo AND Department Loc NYC USING A VIEW TO ADD OR CH...

Page 152: ...ATE Staff_Info SET Department 300 WHERE Number 10024 changes the department number from 200 to 300 entered for Crowell in the preceding INSERT statement To update a table via a view you must have the...

Page 153: ...serted or changed through the view must satisfy any constraints specified in the WHERE clause A view is a useful device for allowing other users to access table data However as the preceding examples...

Page 154: ...mmands Parameters that are specified each time the macro is executed Regardless of the number of DBC SQL statements in a macro the DBC 1012 Data Base Computer treats the macro as a single transaction...

Page 155: ...ample assume that you have entered the statement shown below to create a macro named NewEmp in Personnel the default data base CREATE MACRO NewEmp number INTEGER name VARCHAR 12 dept INTEGER 100 TO 90...

Page 156: ...rameter value in the EXECUTE statement that does not conform to specified formats data types and defaults an error message is displayed For more information about default controls refer to Chapter 3 o...

Page 157: ...ny user may display it using the COMMENT statement To display the stored comment on NewEmp enter COMMENT ON NewEmp Note that if you enter a COMMENT statement for a macro table or view that has the sam...

Page 158: ...at supplies its name and required parameters If you have created the macro for another user you must grant to that user the privilege of executing it For example entering the statement EXECUTE NewEmp...

Page 159: ...acro is executed in Unformat mode column headings for each statement result appear only once at the beginning of the result Processing messages and results that exceed the size of a single display scr...

Page 160: ...st recent REPLACE MACRO statement used to modify it would be displayed Using the edit commands described in Chapter 3 you now modify the CREATE MACRO statement as follows so that NewEmp may be used to...

Page 161: ...me of the data base in which the macro is contained For example to rename the macro NewEmp to AddEmp as in the previous example if your default data base setting is other than Personnel enter the foll...

Page 162: ...or failure of macro execution and the text of any echoed remark before displaying the result processing messages for individual statements within the macro are suppressed When a macro is executed in...

Page 163: ...ou must have the drop privilege for the macro refer to Chapter 10 9 9 SUMMARY AND PREVIEW This chapter showed you how to use macros The next chapter shows you how to use data definition statements to...

Page 164: ...e data base called DBC DBC is usually managed by the system administrator To protect the security of system tables within DBC that are used by DBC 1012 software the system administrator usually assign...

Page 165: ...macros from a data base SELECT Query data in a table or view INSERT UPDATE and DELETE Insert modify and remove rows from a table EXECUTE Execute a macro in a data base GRANT Grant any privilege to ano...

Page 166: ...e new data base Jones in turn allocates portions of the Finance data base to create Personnel and other department data bases as well as users Chin and Greene as shown in Figure 10 1 Figure 10 1 Creat...

Page 167: ...ata bases and lsers Examples in the chapter assume that you have been granted the necessary privileges 10 2 GRANTING PRIVILEGES fter being granted one privilege you may automatically have ther privile...

Page 168: ...to grant the privilege INSERT Insert for table When a view is used to insert table rows Insert privilege for view is needed MODIFY DATABASE Drop Data Base MODIFY TABLE Drop Table MODIFY USER Drop Use...

Page 169: ...view created in Chapter 6 To grant limited select insert and update privileges on Employee to Peterson and Moffit enter the following statement GRANT SELECT INSERT UPDATE ON Employee_Info TO Peterson...

Page 170: ...base enter GRANT ALL BUT CREATE TABLE ON Greene TO Peterson 10 2 3 Granting privileges to a Group of Users Use the ALL keyword to grant one or more privileges to a group of users For example to enable...

Page 171: ...from space belonging to the system administrator The FALLBACK option specifies that a secondary copy of any table created in Jones user space is maintained in addition to the primary copy for use in...

Page 172: ...12 The DEFAULT DATABASE option identifies Finance as the default data base for every DBC SQL statement that Jones enters after logging on This eliminates the need for Jones to qualify with Finance the...

Page 173: ...s AS PASSWORD VP Except for the new password Jones user definition remains as specified in th CREATE USER statement above If Jones wishes to increase the disk space as well as the space allocated to s...

Page 174: ...ting tables that use the original default journal option will retain the original journal setting To change the journal option for an existing table you must explicitly change it in an ALTER TABLE sta...

Page 175: ...IP To delegate responsibility for all Personnel matters to controller Chin Jones transfers ownership of the Personnel data base to Chin using the following GIVE statement GIVE Personnel to Chin Jones...

Page 176: ...tionary Directory information in two ways 1 By querying system defined views of Data Dictionary Directory tables 2 By entering the DBC SQL HELP statement for a data object This chapter shows you how t...

Page 177: ...ent or the HELP statement described below you may obtain a description of any of these views For example entering COMMENT ON DBC UserGrantedRights displays the following description The DBC UserGrante...

Page 178: ...pe of journal table maintained for tables created in a data base PermSpace Specifies the total space in bytes allocated to the data base CommentString Contains the text of any user supplied descriptio...

Page 179: ...tion is in effect for the table as follows Y yes fallback is the default N no no fallback is the default JournalFlag Specifies the default for the type of journal table maintained for the table Creato...

Page 180: ...g columns are defined DataBaseName Identifies the data base in which the table view or macro resides TableName Identifies the table view or macro ColumnName Names the column or parameter ColumnFormat...

Page 181: ...the column or parameter DecimalTota1Digits Specifies the total number of digits left and right of the decimal point for a decimal field DecimalFractiona1Digits Specifies the number of fractional digit...

Page 182: ...vides information about a privilege that you have granted to another user on a data base table view or macro The following columns are defined DataBaseName Gives the name of the data base on which the...

Page 183: ...that you are Jones have granted to other users enter SELECT DataBaseName TableName Grantee AccessRight FROM DBC UserGrantedRights ORDER BY 1 2 3 4 The result is shown below DataBaseName TableName Gra...

Page 184: ...tion about privileges that you have been granted on the Personnel data base enter table that have been granted to you enter SELECT DataBaseName TableName AccessRight FROM DBC UserRights WHERE DataBase...

Page 185: ...enter SELECT FROM DBC SessionInfo WHERE UserName Greene The result is shown below UserName AccountName SessionNo DefaultDataBase Partition Greene F A OOOOEE03 Personnel DBC SQL 11 2 USING THE HELP ST...

Page 186: ...wner of the object or have any privilege on it When HELP DATABASE is entered the DBC 1012 lists all tables views and macros in the specified data base and the following information about each Name Typ...

Page 187: ...INDEX statement is used to provide information about all the indexes or a selected index of a table The response to the HELP INDEX statement includes the following information about each index Unique...

Page 188: ...st requirel Employee number required Department number required Title or position Annual base salary Years of experience The display is truncated other fields occur to the right The statement HELP COL...

Page 189: ...11 14 C09 OOOI 07...

Page 190: ...9 78 08 08 UNIQUE PRIMARY INDEX EmpNo 10014 OEI 0002 83 01 H 20 0 AP1 0001 A P Payab1 DB Deslgn 82 08 09 83 04 10 83 04 10 INDEX Name 10002 OEI 0001 83 04 15 33 5 ENG 0003 Deslgo idg t rame 18 01 02 8...

Page 191: ......

Page 192: ...cters indicate that a value or name is to be substituted in their place Underscores indicate the default value Special characters including blanks are required as shown unless specifi d otherwise Brac...

Page 193: ...name datadesc ADD cname datadesc DROP cname DROP cname Any of the following options may be listed in any order NO FALLBACK PROTECTION NO AFTER JOURNAL DUAL BEFORE WITH JOURNAL TABLE tname BEGIN TRANSA...

Page 194: ...ENT n BYTES option option Any of the following options may be listed in any order SPOOL n BYTES ACCOUNT aeetid NO FALLBACK PROTECTION NO AFTER JOURNAL DUAL BEFORE DEFAULT JOURNAL TABLE tname CREATE UN...

Page 195: ...PROTECTION NO AFTER JOURNAL DUAL BEFORE WITH JOURNAL TABLE tname CREATE USER username FROM ownerdb AS PERM ANENT n BYTES PASSWORD name opt i on opt i on Any of the following options may be listed SPOO...

Page 196: ...ame IN READ WRITE SELECT expr expr FROM tname aname tname aname WHERE eond DATABASE dbname WHERE eond DEL ETE FROM tname aname ALL DATABASE DEL ETE dbname USER DATABASE DROP name USER DROP INDEX ename...

Page 197: ...ALL username WITH GRANT OPTION DATABASE dbname USER username TABLE tname VIEW viewname MACRO maeroname COLUMN ename enamel FROM tname tname COLUMN FROM tname HELP COLUMN tname cname ename COLUMN tname...

Page 198: ...T JOURNAL TABLE tname DROP DEFAULT JOURNAL TABLE tname MODIFY USER username AS option option Any of the following options may be listed in any order PERM ANENT n BYTES PASSWORD name SPOOL n BYTES STAR...

Page 199: ...e cname enamel AS ACCESS DATABASE dbname FOR EXCL USIVE LOCK ING TABLE tname SHARE MODE VIEW vname IN READ WRITE SEL ECT expr expr FROM tname aname tname aname WHERE cond ALL PRIVILEGES REVOKE privile...

Page 200: ...tname aname WHERE cond GROUP BY expr expr HAVING cond DESC DESC ORDER BY expr expr ASC ASC WITH summarylist BY breaklist J MACRO macroname SHOW TABLE tname VIEW viewname UPD ATE tname aname SET cname...

Page 201: ...lphabetically These modifiers can be used with any DBC SQL statement EXPLAIN statement ACCESS DATABASE dbname FOR EXCL USIVE LOCK ING TABLE tname SHARE MODE VIEW vname IN READ WRITE statement USING na...

Page 202: ...Q COMMANDS ITEQ commands are listed alphabetically Defaults are underscored ABORT ADD BACKWARD SKIP n BWD 1 BWDS CAN CEL CLEAR INPUT n DO WN 3 FILE name FORWARD SKIP n FWD 1 FWDS INPUT JOIN C09 0001 0...

Page 203: ...n LE FT 52 LOGOFF LOG ON tdpid username password acctid PRINT name QUIT RECALL REMARK string RMK REM OVE n RI GHT 52 SET DEFAULTS SD ON SET FORMAT OFF SFO SFF B 12 C09 0001 07...

Page 204: ...INPUTAREA SIZE n SIS 3 SET NULL AS string SNA SET PAGELENGTH n SPL 55 SET PFn command SET RTITLE string SRT ON SET SUPPRESS OFF n SSF SSO ALL SET WIDTH n SW 132 SHO W SHOW CONTROL J SC C09 OOOl 07 n n...

Page 205: ...SHOW SM SV ST SPLIT SUB MIT n UP 3 B 14 MACRO VIEW TABLE objname C09 000l 07...

Page 206: ...SET DATA DDNAME ddname LIMIT n INDICDATA DDNAME ddname LIMIT n EXPORT REPORT DDNAME ddname LIMIT n DIF DATALABELS DDNAME ddname RESET n GOTO 1abe1name HANG n HELP BTEQ ERRORCODE IF operator n THEN com...

Page 207: ...UIT n EXIT ERRORCODE REMARK string string string n REPEAT RUN DDNAME ddname SKIP n SET DEFAULTS OFF SET ECHOREQ ON OFF n n SET FOLDLINE ON ALL SET FOOTING string OFF SET FORMAT ON SET HEADING string O...

Page 208: ...g OFF n n SET OMIT ON ALL n OFF n n SET PAGEBREAK ON ALL SET PAGELENGTH n OFF SET QUIET ON OFF SET RECORDMORE ON SET RETLIMIT n OFF SET RETRY ON SET RTITLE string SET SEPARATOR string n SET SESSIONS n...

Page 209: ...FF n n n SET SKIPDOUBLE ON ALL OFF n n SET SKIPLINE ON ALL OFF n n SET SUPPRESS ON ALL OFF n n SET TITLEDASHES n n n OFF SET TRANSLATE ON OFF n n SET UNDERLINE ON ALL SET WIDTH n SHOW CONTROL S SHOW V...

Page 210: ...TDP nn nnnnnn optional form for VM users only TSO string C09 0001 07 B 19...

Page 211: ...age AccountName UserName CPUTime DiskIO DBC Children Child Parent DataBaseName TableName ColumnName ColumnFormat ColumnTitle ColumnType DBC Columns ColumnLength DefaultValue RangeLow RangeHi Nullable...

Page 212: ...aved IndexPresent DupeDumpSet CreateDate CreateTime EventNum EventType DBC Events_Configuration UserName LogProcessor DBC Events_Media PhyProcessor ProcessorState RestartSeqNum CreateDate CreateTime E...

Page 213: ......

Page 214: ......

Page 215: ...tectionType JournalFlag CreatorName RequestText CommentString AMP DataBaseName AccountName DBC TableSize TableName CurrentPerm PeakPerm DataBaseName TableName DBC UserGrantedRights Grantee AccessRight...

Page 216: ...convenience this appendix lists all default PF key assignments in one place so that you need not refer back and forth between tables in different chapters In the tables below the heading 87 key desig...

Page 217: ...Table C 2 Default PF Keys for Display Commands 87 Key 75 Key Command _ _ PFl9 PF7 BACKWARD PF20 PF8 FORWARD PF22 PFIO LEFT PF23 PFll RIGHT C 2 C09 OOOI 07...

Page 218: ...KS ITEQDSKS DATA ITEQDSK6 ITEQDSK6 DATA ITEQDSK7 ITEQDSK7 DATA ITEQDSK8 ITEQDSK8 DATA Use ddnames ITEQPRTI through ITEQPRT3 file names ITEQPRTI DATA through ITEQPRT3 DATA to define print output files...

Page 219: ...format Assigns the file to output class B After defining a print output file during your ITEQ session you may send the current result to be printed on 8 5 by II inch paper by executing the following I...

Page 220: ...hen be kept and cataloged for later use For example you may establish file attributes and define an output file during your ITEQ session using the following commands Under TSO tso attrib dbcparms lrec...

Page 221: ...n as discussed above the result is stored and catalogued in your directory as ITEQDSK2 ITEQDSK2 DATA AS For complete information about the ITEQ FILE command refer to the DBC 10I2 Data Base Computer Re...

Page 222: ...used with LIKE operator 6 14 character used with LIKE operator 6 14 ANY operator 6 ll ABORT command 3 18 3 21 ABORT statement 3 l8 9 4 9 5 access lock 6 37 account identifier 2 1 3 3 11 10 ACCOUNT op...

Page 223: ...l see also COBOL Preprocessor l l arithmetic expressions 6 l5 6 21 date 6 26 6 27 in UPDATE statement 8 3 views 7 15 with aggregate result 6 23 arithmetic operators see arithmetic expressions AS keyw...

Page 224: ...9 3 columns see also relational data base adding and dropping 7 l2 attributes 7 2 7 5 7 10 changing attributes 7 l2 7 13 default control 7 6 format 7 6 title 7 6 Columns view 11 2 11 5 comma 6 l 8 2...

Page 225: ...data type 7 5 DBC SQL 1 1 DBC 1012 Data Base Computer 1 l 7 1 DECIMAL n m data type 7 4 default control phrases 7 6 default data base see CREATE USER and DATABASE statements default database MODIFY U...

Page 226: ...BLE statement 7 6 7 7 CREATE USER statement lO 8 fie1d see relational data base FILE command 2 2 3 29 FLOAT data type 7 4 format characters 5 l0 format commands BTEQ 5 l5 5 17 5 19 ITEQ 5 3 5 4 macro...

Page 227: ...command 3 10 INSERT statement 8 2 8 5 embedded query 7 10 in a view 8 4 8 5 macro 9 2 restrictions 8 1 INTEGER data type 7 4 Interactive Teradata Query see ITEQ INTERSECT operator 6 17 6 19 IS NULL o...

Page 228: ...7 CREATE USER statement lO 8 MODIFY USER statement lO lO Journals view 11 2 language preprocessors see COBOL Prepocessor LEFT command 3 22 3 28 5 2 LIKE operator 6 l4 6 15 6 34 locking table 6 37 CREA...

Page 229: ...15 NULL phrase 7 6 null values default 7 3 8 2 default blank in report 5 2 search criteria 6 16 specifying character for report 5 5 OR operator 6 9 ORDER BY clause 6 6 6 7 6 32 6 33 ownership data bas...

Page 230: ...printing a report 5 l2 5 13 privilege codes ll 8 privileges lO l 10 3 for statement entry 10 4 10 5 granting 10 4 implicit 10 5 processing message 3 7 3 11 3 24 3 26 5 1 9 9 Format mode 5 1 9 6 Unfor...

Page 231: ...abbreviation for SELECT 6 4 SELECT statement 3 S 3 22 6 3 6 9 6 12 6 21 6 23 6 32 6 33 BTEQ 4 7 embedded query 8 2 self join 6 34 6 35 semicolon 3 3 4 2 6 1 9 3 SessionInfo view ll 2 11 9 SET DEFAULT...

Page 232: ...a result 2 2 D 1 string expressions 6 28 concatenation operator 6 28 string functions 6 28 string functions 6 28 INDEX 6 30 6 31 SUBSTR 6 30 SUBMIT command 3 10 3 17 3 18 3 23 subqueries 6 36 6 37 SU...

Page 233: ...24 5 3 5 5 display commands 5 l macro result 9 6 9 9 UNION operator 6 l7 6 lB unique index 7 l0 UP command 3 l0 3 12 3 15 UPDATE statement 8 3 constraints B 3 view B 5 UPPERCASE option 7 6 user identi...

Page 234: ...4 print file 5 13 D l D 2 PROFILE EXEC 3 4 storing results 3 29 D 3 WHERE clause 6 5 6 12 in a join 6 34 in a self join 6 35 in UPDATE statement 8 3 WIDTH command 5 12 WITH clause 5 7 5 8 5 11 specify...

Page 235: ......

Search results

Summary of Contents for C09-0001-07

Reviews:

Brands by name

Popular brands

Teradata C09-0001-07, User Manual

Search results

Summary of Contents for C09-0001-07

Reviews:

Brands by name

Popular brands