Home

Product Menual - SQLGate for SQL Server

1. Select a printer to print out after popping the Choose a Printer window up Page Size Select paper to print Direction Select orientation of print paper vertical or landscape Pages Landscape Select how many pages to print widthwise Vertical Select how many pages to print palewise Width Set width to print on print paper by dragging mouse Printer Setting Back to previous window Find Table Object l Find Table SS Table to find Select Edit gt Find and Replace gt Find on the main menu or press the shortcut key lt Ctrl F gt to open the Find window Input a word to search among table names at the Keyword If you click the OK it finds out that table by moving working area of Canvas after finding table object which has keyword Press the lt F3 key to find next table 93 gt http www antwiz com 8 Trivia 8 1 Common Edit Search SQL HISTORY Query Editor Options Help lt 94 gt Table of shortcut keys New SOLGate for DB2 User Manual Ctrl N Cut fee poo Comment Block Uncomment Block Shift Ctrl Increase Indent aea CO CET CT Do oun Stop Run Current SQL and Modify Ctrl Alt D Execution plan of current SQL O Code Auto Template Completion Ctrl X Ctrl C Ctrl V Ctrl A Del Ctrl 1 Tab Shift Tab F4 Shift Ctrl F Ctrl Alt Num Ctrl Num Ctrl G C
2. Parameter Display parameter information of the selected object Properties Display properties of the selected object Privilege Display granted privileges to the selected function Reference You can check objects that the selected function refers to Referenced By You can add referenced by objects of the selected function Object gt Go administrator SAMPLE30 1 1 System Schemas B ADMINISTRATOR Tables 38 Views 22 ES Constraints 34 Indexes 31 p Procedures 4 F f Functions 1 m Packa ell New Function Trigge A Edit Aliase ve Drop la Seque Distinc Script Report H 2 Struct V i View Edit Privilege 0 Nickna HR Y Filter a Q vum Refresh a RESI L N w S m ba T gt Object Explorer administrator SAMPLE30 gt el WD GJ Ou un 21 Sa Context Menu of Function New Function The New Function window pops up Edit Modify the selected function script lt 79 gt m http www antwiz com 5 7 e Tab Information of Trigger Properties You can check properties of the selected trigger Reference You can check objects that the selected trigger refers to Scripts You can add referenced by objects of the selected trigger e Context Menu of Trigger Object Explorer administrator SAMPLE30 ew DO_N Object SCH W administrator SAMPLE30 System Schemas
3. yt him Non Identifying Relationship yi on the ER Design tool after that click original table object and target table object in order logical relationship of tables will be set Change Relationship object If you double click the relationship object the Change Relationship window will pop up Relationship a Relationship Name Relationship Type EMP MANAGER FK iin w Logical Relationships Table Source Table EMPLOYEES Target Table EMPLOYEES Relationship Keys Source Column Name Target Column Name MANAGER_ID EMPLOYEE_ID Add Delete Comment Relationship Type Select one among 1 1 1 n nim Logical Relationships When checked it displays as dotted line Source Table Original table of relationship is displayed Target Table Target table of relationship is displayed Relationship Keys Relationship column lists between original table and target table are displayed Source Column Name Column list of original table which is related to column of target table is displayed Target Column Name Column list of target table which is related to column of original table is displayed Add lf you click the Add you can input columns which are going to be target of relationship key additionally Delete If you select relationship key list and then click the Delete the selected relationship key will be deleted Comment Input description of relationship 91 gt http www
4. Moreover elements will be chosen automatically in Element when you select some of sentences in the bel ow editor e Code Template Add Edit or Delete code template General Font _ Highlight Code Template Keyword Template Auto Completion Code Template lt Ctrl J gt Name Description select select from selectw select from where dedare declare begin end cursor ifwend SELECT column list FROM table list WHE ern rem FS declare begin lt caret gt end x If you press lt Ctrl j gt in editor template will be displayed lt 17 gt http www antwiz com e Keyword Template SOLGate for DB2 User Manual Add Edit or Delete keyword template Editor Options xs General Font _ Highlight Code Template Keyword Template Auto Completion Keyword Template Group Keyword Code EXCEPT ACCESS_INTO_NULL ACCESS_INTO_NULL a ze EXCEPT COLLECTION IS NULL COLLECTION IS NULL EXCEPT CURSOR ALREADY OPEN CURSOR ALREADY OPEN Edit EXCEPT DUP_VAL_ON_INDEX DUP_VAL_ON_INDEX EXCEPT INVALID_CURSOR INVALID_CURSOR Delete EXCEPT INVALID NUMBER INVALID NUMBER EXCEPT LOGIN DENIED LOGIN DENIED EXCEPT NOT LOGGED ON NOT LOGGED ON EXCEPT NO DATA FOUND NO DATA FOUND EXCEPT OTHERS OTHERS v 4 mu H Code COLLECTION IS NULL x Select Table Auto Completion in Auto Completion tab to activa
5. Procedures C EMPLOYEE Source Report E E EMPMDC Functions a Cl EMPPROJACT Source Report lt O emp PHOTO 7 Triggers O EMP_RESUME ovil ms TT EXPLAIN ARGUMENT R rue Scripts C EXPLAIN INSTANCE Pon C EXPLAIN OBJECT Sequence Scripts O EXPLAIN_OPERATOR Distinction types Distinction type Scripts Export File z Design Preview Print Cancel administrator SAMPLE30 Count 1 Select a type of report to print out at left tree Select a schema and object to print out at right panel Select font to print out at the Report Font If you click the Design button you can design reports If you click the Preview button you can it in advance a PE PL w oa If you click the Print button report will be printed out x If you click the Export File button you can select format to print out reports PDF EXCEL RTF BMP JPG GIF TEXT E Mail HTML lt 50 gt http www antwiz com e Design Report 4 Fast pk ER bet Men Her osa lara oc mala F Arial vs xs BIU TAY al EE ES ES al ES X Code Data Page1 amp Object 3 Report tr OO EC EROEM CO 1Bo 14 151 BF Q Paget Tf v F EE wN m TableInfo TabName Ci Oh AN Ben AN AN AE 1 0 a 9 Bo F Ga 56 1 4 Select a report at left tree Click the Design button You can edit design to other formats that you want to print out Select
6. Scripts You can inquire creation script by options Context Menu of Nickname e MK Er AE7 administrator SAMPLE30 ei DEPART2 DES MA Ga SHO W administrator SAMPLE30 2a o O AAS A30 ier o ADMINISTRATOR pre a E HS we E pa 2 DEP e oi Hat za e ace z e ag ZZNAQ p ET e e MAND 2 ee 825 e la ABA o 2 FB HAE L PASE BAG i aan l zo NULLID 13300 o H soi a Ha o H SYSTOOL mm Drop ABBE DAP gt BEI AS DAR a en New Nickname The New Nickname window pops up Edit The Edit Nickname window of the selected nickname pops up Drop Drop the selected nickname Script Report Show script reports of the selected nickname Filter Filter and sort lists by using the Filter window lt 85 gt SOLGate for DB2 User Manual Inquires nickname list by the selected owner and detail information and performs overall features about nickname http www antwiz com SOLGate for DB2 User Manual 6 Query Builder You can create quires only by draging amp dropping easily and check results by using Query Builder You can join sort and filter query objects query view column and so on with simple mouse operation and check results by creating various queries Select File gt New gt Query Builder on the main menu or click the El button on the toolbar o Select Table View Open the Object Panel by selecting View gt O
7. Comment _ Properties WHEN Clause Body Trigger Object Schema ADMINISTRATOR Table Trigger Action 0 BEFORE AFTER INSTEAD OF Event 0 INSERT DELETE UPDATE of Columns Referencing Old rows as Old Table as New rows as New Table as FOR EACH Condition ROW STATEMENT View SQL OK Cancel administrator SAMPLE30 Specify a Schema Input a name of creating trigger at Name Specify each items at the Properties tab Check SQL by clicking the View SQL button Click the OK button Soe a th ui eo Properties BEFORE Specifies that the associated triggered action is to be applied before any changes caused by the actual update of the subject table are applied to the database It also specifies that the triggered action o f the trigger will not cause other triggers to be activated AFTER Specifies that the associated triggered action is to be applied after the changes caused by the act ual update of the subject table are applied to the database INSTEAD OF Specifies that the associated triggered action replaces the action against the subject view On ly one INSTEAD OF trigger is allowed for each kind of operation on a given subject view INSERT Specifies that the triggered action associated with the trigger is to be executed whenever an INSE lt 43 gt http www antwiz com 3 5 8 SQLGate for DB2 User Manual RT operation is applied to the subject table or subject view DE
8. Delete Drop the selected index Rename Change name of the selected index Object Reports Script Report Show report of the selected index lt 71 gt http www antwiz com SOLGate for DB2 User Manual View Edit Privilege Inquire and edit granted privileges to the selected index Filter Filter and sort lists by using the Filter window 5 5 Procedures Inquires procedure list of the selected owner and detail information and performs overall features about procedure such as creation modification deletion and so on Object Explorer administrator SAMPLE30 Co Ce a 2 HJGHJTYH Creation Date 2011 04 07 PM 5 20 28 Last Modified 2011 04 07 PM 5 20 28 S Some Referenced By W administrator 2SAMPLE30 ZAGIAR System Schemas IRENE NAAA ATT a ADMINISTRATOR 2 bje Nan ADMINISTRATOR GH Tables 38 3 Creation Da 2011 04 5 20 28 PM Views 22 A Last Modified 2011 04 07 5 20 28 PM 83 Constraints 34 5 Status Yes Indexes 31 lt n AO EA AROS AS AA ASES 5 ER Procedures 4 12 DROP SPECIFIC PROCEDURE FGH E ABCD2 zi G 21 9 CREATE PROCEDURE ADMINISTRATOR hjghjtyh PI on 10 SPECIFIC fgh P MEDIAN_RESULT_SET j 11 LANGUAGE SQL E MEDIAN_RESULT_SET2 142 NOT DETERMINISTIC f Functions P COMMIT ON RETURN NO Packages lt 14 EXTERNAL ACTION E Triggers 15 MODIFIES SQL DATA ji Ali 16 CALLED ON NULL INPUT fg Sequences 17 INHERIT
9. If you select keyword in SQL Query Editor and then press the shortcut key SQL command will be executed lt 60 gt http www antwiz com SOLGate for DB2 User Manual pRun P gt so Dd ele amp aaa amp amp amp B 1B OO FBO XEBDNX Zon Ta PAE A Oo 00 S a HIREDATE JOB 1995 01 01 2003 10 10 2005 04 05 1979 08 17 2003 09 14 2005 09 30 2000 08 15 2000 06 19 1988 05 16 1993 12 05 2001 07 28 2006 12 15 ajojsjojajoj gt r Above example is result screen of execution of lt Alt 1 gt command after selecting EMPLOYEES text X SQL Desc EMPLOYEES e SQL Conversion Specify using format when convert SQL Specify Escape character and separator conversion format for each program languages Separator Close SQL Clear SQL Add amp VarFirstRowSQL SQL Add amp VarNextRowSQL lt 61 gt http www antwiz com 3 7 3 7 1 3 dad 3 7 3 3 7 4 Window Tile Horizontally Tiles windows horizontally Select Window gt Tile Horizontally on the main menu Tile Vertically Tiles windows vertically Select Window gt Tile Vertically on the main menu Cascade Tiles windows in cascade Select Window gt Cascade on the main menu Arrange Icons Arranges window icons Select Window gt Arrange Icons on the main menu lt 62 gt SQLGate for DB2 User Manua
10. If you select File gt Open lt Ctrl 0 gt on the main menu Open File dialog will be shown If you select a file the file will be opened on SQL Query Editor If SQL Query Editor doesn t exist the file will be opened after executing new SQL Query Editor Recent Files If you select File gt Recent Files on the main menu list of recently opened files is shown If you select a file the file will be opened on SQL Query Editor If SQL Editor doesn t exist the file will be opened after executing new SQL Query Editor Save If you select File gt Save lt Ctrl S gt on the main menu you can save contents on SQL Query Editor Save As If you select File gt Save As lt Ctri Shift S gt on the main menu you can save contents on SQL Query Editor as new name File Encoding If you select File File Encoding on the main menu you can convert encoding type of SQL Query Editor into ANSI Unicode Swapped Unicode or UTF 8 File Encoding le ANSI File Type gt Unicode Close Window Swapped Unicode Close All Windows UTF 8 gn 2 p lt 10 gt http www antwiz com 3 1 11 3 1 12 3 1 13 3 1 14 3 1 15 3 1 16 3 1 17 SQLGate for DB2 User Manual File Type If you select File gt File Type on the main menu you can convert file type of SQL Query Editor into Default Windows CR NL Macintosh CR or Unix NL Close Window If you select File gt Close Win
11. Object Explorer administrator SAMPLE30 gt y AAAA Object To 3 W administrator SAMPLE30 H 2 A System Schemas gess 3 A ADMINISTRATOR me m Tables 38 2 mn Ce e New Table SA ad amp Edit t H sf ma Drop Truncate 7 3 E o Copy Row Count b Ze 2 New Alias ZE Rename F2 Eg E Object Reports gt Script Report gt ZE f n Export Data St S Import Data lomm ES E Reorg Table af aA EJ E EZ View Edit Privilege AP Fitter Fat Refresh EL e New Table Create Table window will be popped up Add Column Add columns to the selected table Constraint Add constraints to the selected table Index Add indexes to the selected table Trigger Add triggers to the selected table Alter Change column and comment of the selected table Delete Truncate Execute Drop Truncate command to the selected table Copy Copy the selected table Provides Only Table Structure Table Structure and Data Row Count Display record count of the selected table s data New Alias Create an alias of the selected table Rename Change name of the selected table Object Reports Print out information of the selected table as reports Script Report Print out script of the selected table as reports Export Data Export the selected table Excel Csv SQL and so on Import Data Import the selected table
12. E 000010 CHRISTINE A HIREDATE 1995 01 01 JOB PRES 000020 MICHAEL 000030 SALLY 000050 JOHN 2003 10 10 2005 04 05 1979 08 17 MANAGER MANAGER MANAGER 000060 IRVING 000070 EVA 2003 09 14 2005 09 30 MANAGER MANAGER 000090 EILEEN 000100 THEODORE ja o s o 7 a r 4 000110 VINCENZO ILUCCHESSI 2000 08 15 MANAGER 2000 06 19 IMANAGER 1988 05 16 SALESREP 000120 SEAN O CONNELL 000130 DELORES M QUINTANA SEET 1993 12 05 2001 07 28 CLERK ANALYST DEER A XV a 4 N Data Execution plan Message History 3 3 3 SQL Align Options It is option for alignment or indentation feature of created SQL Select View gt SQL Align Options on the main menu Align and Indent V Align Keyword Right Relational Operator Align lt lt gt gt Align Operator L AS Keyword Align Align Line Comments 7 Indent use tab character 4 Line Feed when Less than Less Right Margin Operator lt gt Block Indent Width v And Or Indent Comma 9 Unchanged No Space After Space Prior Space Prior After Space Default Set Upper Lower case Clear Comment Rem
13. E01 6789 EE D11 6423 b INSERT Script D21 7831 gt UPDATE Script Ell 5498 NE Sit E21 0972 ADO 3490 ADO 2167 http www antwiz com E e SOLGate for DB2 User Manual Paste Select Paste Select script of the selected object to SQL Query Editor Replace Select Change the Select script of the selected object in SQL Query editor New Tab Select Open Select script of the selected object in new tab New Window Select Open Select script of the selected object in new SQL Query Editor window Drop Script Paste Drop script of the selected table or view to SQL Query Editor Insert Script Paste Insert script of the selected table or view to SQL Query Editor Update Script Paste Update script of the selected table or view to SQL Query Editor Delete Script Paste Delete script of the selected table or view to SQL Query Editor SQL History It saves successfully executed SQLs in SQL Query Editor Check Tools gt Options gt SQL QUERY EDITOR gt AUTO SAVE SQL HISTORY to save SQL history in Program options r H ele Al oa 74 aal BA Sa soL Hel BAI SAM SQL Hi EH MAN EM SEI soL S SM aa HS FHI ol a SM sq 22 JS o MOlS 6 HEIA elch viso JS AIS AS sa AS AM AS o FHE SOL JIS AS AIS D 21 Sp ASPE Ha 1 d AIS Del amp EAH Su soi ge HA wF H S A 21 0 FI amp C AUTO SAVE SQL HISTORY W
14. Editor and then press lt Ctrl gt keys it will be converted into select from wh ere code 1 selectu select 2 from where 4 8 Paste from the code This is a feature to extract only SQL in development code For example it can extract SQL statement only in PHP code that includes SQL code headcolor FF6699 code footcolor FFDDES code bgcolor f9fafb btn type board select user nm passwd email p seq ansnum depth title contents html flag reg date view no from faq where code qna stmt mysql query Sqry if row mysql fetch array stmt user_nm row user_nm email row email passwd row passwd p_seq row p_seq ansnum row ansnum depth row depth title row title contents row contents Copy above PHP code to clipboard and then select the Paste from the code on the context menu in SQL Query Editor or press shortcut key lt Ctri Shift V gt Only SQL statement will be extracted like below ei sal SQL SS HZ administrator SAMPLE30 SQL2 e ba QS BA Alla a a Ss BiBdOo0xi2 a t s OQOFFBER4 wa SQL2 x EI select user_nm passwd email p_seq ansume depth title contents html_flag reg_date view_no 2 i from faq where code qna lt 70 gt http www antwiz com 4 9 Execution plan SOLGate for DB2 User Manual If you press the shortcut key lt F7 gt or the Es button after creat
15. Excel Csv SQL and so on View Edit Privilege Inquire and edit privilege of the selected table Filter Filter and sort lists by using the Filter window Refresh Refresh 73 gt http www antwiz com 5 2 View SOLGate for DB2 User Manual Inquires view list by owner and detail information of view and performs overall features about view such as creation lt 74 gt modification deletion rebuilding and so on po Object Explorer administrator SAMPLE30 Fe za Ch VACT Creation Date 2011 03 24 PM 1 01 02 Last Modified 2011 03 24 PM 1 01 02 Ges Co _ Column W administrator 2SAMPLE30 22 2 7 System Schemas 3 B ADMINISTRATOR Tables 38 Views 22 CD TESTVIEW H VASTRDE1 3 VASTRDE2 vDEPMG1 GH VDEPT GC vEMP CD VEMPDPT1 3 VEMPLP yo y EI VEMPPROJACT BI VFORPLA D VHDEPT CH vIEW DEPT H VIEW TEST GC VPHONE HI vPROJ GH VPROJACT ACTNO SMALLINT N N N ACTKWD CHARACTER N N N Column Data Type NULL Default Identity Specification bit data Comment 1 2 3 ACTDESC VARCHAR N N N m View Comment Save administrator SAMPLE30 Tab Information of View Column You can inquire and print out column information and comment of the selected view Data You can add modify delete and print out data of the selected view Properties You can check properties of the selected view Trigger You can inquire add delete and p
16. Exporting If records exceed 6 hundred thousand a file will be created additionally every 6 hundred thousand by s electing Records Count of Each File X Ex If you input a file name to export as ExportObject xls ExportObject001 xls and ExportObj ect002 xls files will be created 3 Click the Run button lt 53 gt http www antwiz com SOLGate for DB2 User Manual 3 6 4 Import Data You can import data in various formats files such as MS Excel DBF Text CSV File and so on to table by using Import Data Select Tools gt Import Data on the main menu or click the EN button 1 Select atable to import then click the Start button W Import Data administrator SAMPLE30 Ki Import Data E Y administrator SAMPLE30 E B ADMINISTRATOR E AAAA Cl act E act2 E CATALOG E CL_SCHED E CUSTOMER E DEPARTMENT Data Import Wizard sl Data Import wizard k zl File Type File Name Please select the file format of the data you would like to import Ki Select the file that contains the data you would like to import Ki Table Type Import File D Work_SamplesSQL samples test Cu O DBase File dbf Import Wizard Import Wizard Text File txt csv 9 Excel File xls 010 Encoding Type of Original File ANSI Windows Z 019 MS Access File mdb 3 Specify data type Specify Date Time and Number format Data Import Wi
17. Manual Inquires constraint list by owner and detail information of each constraint and performs overall features about constraint such as creation modification deletion and so on po Object Explorer administrator SAMPLE30 fe ez zy FK_PO_CUST _ Information Column Reference Referenced By Scripts W administrator SAMPLE30 a System Schemas 35 PK DEPARTMENT Name Value 3 B ADMINISTRATOR 1 Type Foreign key E ninang 2 Constraint Name FK_PO_CUST Views 22 ren a GE 4 Definer SYSTEM d 5 Table Schema ADMINISTRATOR ar Mt ACT 6 Table Name PURCHASEORDER a ae 7 Table Status Normal a ia Facial 8 Table FK Constraints Checked Checked by system a 1 9 Table Check Constraints Checked Checked by system a j 10 Enforced Yes e TI 11 Check Existing Data Immediately check existing data gt PK_DEPT 12 Enable Query Optimization Yes 83 PK_EMP_PHOTO G PK_EMP_RESUME 3 PK_INVENTORY 3 PK_PRODUCT 835 PK_PRODUCTSUPPLIER B PK_PRODUCTSUPPLIER 83 PK_PROJACT M 4 II Li administrator SAMPLE30 e Tab Information of Constraint Reference You can check objects that the selected constraint refers to e Context Menu of Constraint a Object Explorer administrator SAMPLE30 Ch d Object W administrator SAMPLE30 a System Schemas 3 ADMINISTRATOR Tables 38 Views 22 3 Constraints 34 Ti m 3 YM y CON OU don m afp IP New Constra
18. Name 5 User Directory 1 SELECT FROM PROJACT A 2 3 SELECT FROM PROJECT B 4 WHERE B RESPEMP gt 100 16 8 7 WHERE A PROJNO B PROJNO Cancel IQ administrator SAMPLE30 If you click the OK button after selecting SQL name and group it will be saved Saved user SQL can be checked in SQL HISTORY lt F8 gt lt 31 gt http www antwiz com SOLGate for DB2 User Manual 3 4 9 SQL Align Aligns created SQL in SQL Query Editor automatically Press Query gt SQL Align F9 gt on the main menu or the EI after creating SQL gt E SELECT EMPLOYEE ID FIRST NAME LAST NAME EMAIL PHONE NUMBER HIRE DATE FROM EMPLOYEES c A SELECT EMPLOYEE ID FIRST NAME LAST NAME EMAIL PHONE NUMBER HIRE DATE _ FROM EMPLOYEES c JONAS ME lt 32 gt http www antwiz com SOLGate for DB2 User Manual 3 5 Create 3 5 1 New Table Creates table on database Select Create gt Table on the main menu ED New Table administrator SAMPLE30 n Eon Ex Schema ADMINISTRATOR A Name Comment _ Column Options El ek Column Name Data Type Size Precision Scale bit data Primary Key Not NULL 4 1 CHARACTER vil 2 CHARACTER 1 3 CHARACTER 1 4 CHARACTER 1 a CHARACTER 1 6 CHARACTER 1 7 CHARACTER 1 8 CHARACTER 2 9 CHARACTER 1 10 CHARACTER 1 w 4 I View SQL OK Cancel administrator SAMPLE30 e C
19. Properties and so on Access Data NO SQL Represents that the method cannot execute SQL CONTAINS SQL Represents that SQL which didn t read or modify SQL data cannot execute by the method lt 48 gt http www antwiz com SOLGate for DB2 User Manual READS SQL DATA Represents that SQL which didn t modify SQL data cannot be included to the method Scratchpad If you specify the SCRATCHPAD additional factor is sent to the external method which specify the scratch pad whenever it calls user defin ed method 4 Click the OK button 3 5 12 New Nickname Defines a New Nickname Select Create Nickname on the main menu If you set an alias you can control remote objects in remote ED New Nickname administrator SAMPLE30 o mm Schema ADMINISTRATOR y Name test Comment _ Properties Columns Server Information Remote Object Information Name Schema SAMPLE ADMINISTRATOR y Table name DB2 UDB CATALOG Y Options ek Cem e View SQL OK Cancel administrator SAMPLE30 To call server information you must register Wrapper after turning on Federated System After that Server Information and Remote Object Information can be called DB2 server is called Federated Server in Federated System You can configure DB2 Instances as much as wanted count to operate as Federated Server Moreover you can use the existing DB2 Instanc
20. SPECIAL REGISTERS a 18 OLD SAVEPOINT LEVEL Distinction types 4 BEGIN Structured type 20 Nicknames 21 END o Q HR 4 uw p 4 w r administrator SAMPLE30 e Tab Information of Procedures Scripts Display creation script of the selected object Parameter Display parameter information of the selected object Properties Display properties of the selected object Privilege Display granted privileges to the selected procedure Reference You can check objects that the selected procedure refers to Referenced By You can add referenced by objects of the selected procedure e Context Menu of Procedures Object Explorer administrator SAMPLE30 zy ABCD Object Bes Wi administrator SAMPLE30 a 2 f System Schemas ADMINISTRATOR 2 Tables 38 3 Views 22 4 Lei Constraints 34 5 Indexes 31 lt 6 Procedures 4 RE MET 1 Pp HJ ET ME amp Modify E ME f Functic d Packac Script Report gt 7 E Trigget 2 View Edit Privilege Aliases gi ra h New Procedure mm Drop f Sequet Y Filter 2 Distinc gt Refresh 4 Structured type 20 New Procedures The New Procedure window pops up Modify Modify the selected procedure script Drop Drop the selected object Script Report Show script reports of the selected procedure 78 http www antwiz com 5 6 View Edit Privil
21. SUPPLIERS PRODUCTSUP EMPLOYEE EMPNO CHARACTER 6 TEST FIRSTNME VARCHAR 12 AAAA MIDINIT CHARACTER 1 EMPLOYEE LASTNAME VARCHAR 15 WORKDEPT CHARACTER 3 PHONENO CHARACTER 4 Canvas DATE CHARACTER 8 SMALLINT CHARACTER 1 BIRTHDATE DATE SALARY DECIMAL 9 2 BONUS DECIMAL 9 2 COMM DECIMAL 9 2 administrator SAMPLE30 e ER Design Tools SI It is a feature to select object in drag area by dragging mouse Select Move D Move the selected object H Resize Resize group area Delete Xy Delete the selected object Panning Hand EI Move working area by dragging mouse Zoom Out A Zoom out Canvas Zoom In A Zoom in Canvas Region EI Add the Region object which ties up objects into one group Text Box e Add sentence with simple contents 88 gt http www antwiz com SOLGate for DB2 User Manual 1 1 Non Identifying Relationship yi 1 Set the 1 1 Non Identifying Relationship between table objects 1 n Non Identifying Relationship Fin Set the 1 n Non ldentifying Relationship between table objects n m Non Identifying Relationship vi 1 Set the n m Non Identifying Relationship between table objects EJ View SQL Inquire creation script of currently designed ERD e Canvas This is working space that you can place ERD objects Table Text Box Region Object on the screen manually You can de
22. Structured Type window pops up Edit The Edit Structured Type window of the selected structured type pops up Drop Drop the selected structured type Script Report Show script reports of the selected structured type Filter Filter and sort lists by using the Filter window lt 84 gt http www antwiz com 5 12 Nickname such as creation modification deletion and so on Object Explorer administrator SAMPLE30 koko e VI DEPART Creation Date 2011 04 15 2 3 33 58 Last Modified 2011 04 15 L 3 33 58 Per Comment Columns Data Properties Scripts Wi administrator SAMPLE30 2a i Y System Schemas PK Columns DataType NULL Default Identity Specification Generated d f ADMINISTRATOR 1 PK1 DEPTNO CHARACTER 3 Not Null N Tables 38 2 PK2 DEPTNAME VARCHAR 36 Not Null N ZC a Mews 3 MGRNO CHARACTER 6 N 2 Constraints 4 ADMRDEPT CHARACTER 3 Not Null N 5 LOCATION CHARACTER 16 N Procedures p Fo Functions Packages Triggers Aliases la Sequences Distinction types Structured type i Nicknames 2 DEPART2 2 HR o H NULLID o Q soi B SYSTOOLS D Hi p administrator SAMPLE30 Tab Information of Nickname Column You can inquire column information of the selected nickname Data You can add and modify data of the selected nickname Properties You can inquire properties list of the selected nickname
23. User Manual Filter Filter and sort lists by using the Filter window 5 9 Sequences Inquires sequence list of the selected owner and detail information and performs overall features about sequence such as creation modification deletion and so on pa Object Explorer administrator SAMPLE30 o 1 O gt Y SAMPSEQUENCE Creation Date 2011 03 24 PM 1 01 03 Last Modified 2011 03 24 PM 1 01 03 Object Com _ Properties W administrator SAMPLE30 Mane Value gt System Schemas 1 SequenceID 6 ADMINISTRATOR 2 Schema ADMINISTRATOR Tables 38 3 Name SAMPSEQUENCE Views 22 4 Remarks Le Constraints 34 5 Owner SYSTEM Indexes 31 lt 6 Definer SYSTEM p0 Procedures 4 d 7 Type Sequence f Functions 1 8 Origin User generated sequence 1 Packages 4 9 Sourcetype BIGINT Triggers 2 10 Start 1 1 ge Aliases 5 11 Increment 1 la Sequences 1 Minimum value 1 H H ESSA bo a SAMPSEQUENCE Maximum value 9 22337203685478E 18 Distinction types Ap LA N 14 Cyding Sequence cannot cyde 42 Structured type 15 Cache 20 O Nicknames 16 Order No o dm 17 Precision BIGINT 4 8 NULLID 18 Created 2011 03 24 PM 1 01 03 a 8 sou 19 Altered 2011 03 24PM 1 01 03 d SYSTOOLS 20 SourceSchema SYSIBM 21 SourceName BIGINT w administrator SAMPLE30 e Tab Information of Sequences Properties You can check sequence information such
24. after auto completion x You can use it optionally when there are too many objects 18 http www antwiz com Display completed keyword by Keyword Auto Completion in disting Selected object by Table Auto Completion in distinguis 3 3 3 3 1 View Object Panel SQLGate for DB2 User Manual Object panel can be docked at the left or right of the main window and it can search and manage objects If you select an object detail information about the object will be shown at the bottom of the window If you click the right mouse button after selecting an object you can add delete edit or manage it You can add SQL to SQL Query Editor simply by using Object Scripting feature You can create various queries and check results with only simple operation Select View gt Object Panel on the main menu or click the dE button fei SOLGate for DB2 SOL Query Editor administrator SAMPLE30 SOLT File Edit View Query Create Tools Window Help DefaultLayout administrator ESAMPLESO A W i SQL Query Editor Fd 3 aaa e 8417 a Object Panel A ban P D Oso b lt amp EI be n ey OOOO ratas 0 Object EE x G 00 E W administrator SAMPLE30 a System Schemas E H ADMINISTRATOR Tables 38 Views 22 aa Constraints A Indexes 31 bo SELECT FROM ADMINISTRATOR EMPLOYEE a E RR E 1 empno n E Cl HIREDATE 1995 01 01 00002
25. as Increment by Last Min Max Value and so on Privilege You can check granted privileges to the sequence Scripts You can check creation script of the selected sequence by options e Context Menu of Sequences Object Explorer administrator 2 SAMPLE30 e Y SAMP Object Com SCH W administrator 8SAMPLE30 a System Schemas 1 2 ADMINISTRATOR 2 Tables 38 3 Views 22 4 83 Constraints 34 5 Indexes 31 6 Procedures 4 diz Functions 1 lg Packages 4 d 9 e A Triggers 2 10 Aliases 5 11 3 f Sequences 1 12 a Distinc ch New Sequence L Struct 4 Edit Nickna Drop o Q HR Script Report r NuD ae n o B squ V i View Edit Privilege g systoou SP Filter GI Refresh FiraAlalmIAIMIALAIALS l ealelimalmaAliAlSeimiainivwti r tlt a New Sequences The New Sequence window pops up Edit The Edit Sequence window of the selected trigger pops up Drop Drop the selected sequence Script Report Show script reports of the selected sequence View Edit Privilege Inquire and edit granted privileges of the selected sequence Filter Filter and sort lists by using the Filter window lt 82 gt http www antwiz com 5 10 Distinct type SOLGate for DB2 User Manual Inquires object type and collection type list by the selected owner and detail information of each types and perf
26. at left tree Select a schema at schema drop amp down list at the top of the screen Check an object to export If you click the Export button DDL script of the selected object will send to editor aoe Se A A You can add several DDL scripts to editor by repeating step 1 to 4 6 If you click the libutton editor will be initialized 7 If you click the ES button exported script in editor sends to SQL Query Editor lt 52 gt http www antwiz com SOLGate for DB2 User Manual 3 6 3 Export Data You can export data in table by using Export Data in various formats such as MS Excel DBF Text CSV File and so on Select Tools gt Export Data on the main menu or click the fan button 1 Select atable to export then click the Start button f n Export Data administrator SAMPLE30 Export Data E m W administrator SAMPLE30 O El EMP PHOTO O E EMP RESUME O El EXPLAIN ARGUMENT Select Table Name ADMINISTRATOR EMPLOYEE Count 1 administrator SAMPLE30 2 Select a file to export after specifying a File Type Data Export Wizard Es Data Export Wizard x File Name Please select the file to export data File Type What kind of format you would like to export Table Type DBase File dbf ID O Text File txt csv 9 Excel File xls Records Count of Each File SQL Script File sql 010 MS Access File mdb Action After
27. faster through this feature If you press shortcut key lt Ctri H gt table auto completion will be executed wherever Db Run P D oz Dd Ke ll A amami ECH ab Y OOl EM DX S 42 9033 AN lsQL2 sal x f bk SELECT FROM ADMINISTRATOR EN Table EMPMDC Table EMPPROJACT Table EMP_PHOTO Table EMP_RESUME Alias EMP Alias EMPACT Alias EMP_ACT Structured Type EMP 69 gt SOLGate for DB2 User Manual add code yellow line is marked in gutter as below Also symbol is displayed on the tab http www antwiz com SQLGate for DB2 User Manual 4 6 Field Auto Completion If you input dot in SQL Query Editor or PL SQL Query Editor alias or field of table will be displayed sal SQL Query Editor administrator SAMPLE30 D WSQL2 sal Run gt i Stop ps A ale a Si la la gt ve 100x244 Hl ZB EE x EI D SELECT FROM ADMINISTRATOR EMPLOYEE A 2 WHERE Al EMPNO CHARACTER 6 n FIRSTNME VARCHAR 12 MIDINIT CHARACTER 1 LASTNAME VARCHAR 15 WORKDEPT CHARACTER 3 PHONENO CHARACTER 4 HIREDATE DATE JOB CHARACTER 8 EDLEVEL SMALLINT z ADMINISTRATOR EMPLOYEE Field Auto Col m 4 7 Code Auto Completion Code Auto Completion is a useful feature that you save often using code in advance and call them whenever you need If you press shortcut key lt Ctrl gt code auto completion will be executed If you input Selectw in SQL Query
28. intellectual property rights The patent right copyright and intellectual property belong to Antwiz Inc http www salgate com SOLGate for DB2 User Manual http www antwiz com 4 SQL QUERY EDITOR 4 1 Multi Query You can execute several SQLs simultaneously and check results in grid Also you can compare data more easily Separator of command is at OWL QUENy UI GUN St atone SAIVIPLOESU d z ELL ban P b Ga be e las aaa mei e asB amp GOxlfratea Zl 0rr OaG gt 00 REL SELECT FROM ADMINISTRATOR EMPLOYEE a SELECT FROM ADMINISTRATOR EMPPROJACT a FIRSTNME CHRISTINE 1995 01 01 MICHAEL 2003 10 10 SALLY 2005 04 05 JOHN 1979 08 17 5 000060 IRVING 2003 09 14 EIERE Jo foot PROJNO AD3100 5 2002 01 01 2002 07 01 AD3110 2002 01 01 2003 02 01 AD3111 2002 01 01 2002 03 15 AD3111 5 2002 03 15 2002 04 15 AD3111 5 2002 03 15 2002 10 15 6 AD3111 5 2002 04 15 2002 10 15 AO l N data Executon plan Messages S Select 1 73 OSec 3 1 Insert Row ANSI DOS w 00 SELECT FROM ADMINISTRATOR EMPPROJACT a al SELECT FROM ADMINISTRATOR EMPLOYEE a gt 4 eieiei oo SF siSisisigialBigliaigigisls 1 73 OSec 3 1 lt 65 gt SQLGate for DB2 User Manual http www a
29. notation No la iii 11 Type can have subtypes No Distinction types 3 lt 12 Length of type can be kept with base table row 178 EH 13 Source Schema SYSIBM m ADDRESST bannan 14 Source Name VARCHAR a DEPT 15 Scale 0 ke EMP 16 Code page 0 gt 17 System calculated inline length 178 a MGR iy PROJECT iy TEST_T d US_ADDR_T x administrator SAMPLE30 Tab Information of Structured type Properties You can inquire properties list of the selected type Attribute You can inquire attribute list of the selected type Method You can inquire method list of the selected type SQLGate for DB2 User Manual Inquires structured type list by the selected owner and detail information and performs overall features about Scripts You can inquire the creation script save to file and send to SQL Query Editor Context Menu of Structured type Object Explorer administrator SAMPLE30 ey ADDR Object Pre gt W administrator SAMPLE30 a 2a System Schemas 3 B ADMINISTRATOR fa Sequences 1 a Distinction types 3 2 Structured type 8 Tables 38 n Views 22 m ES Constraints 34 4 Indexes 31 5 pg Procedures 4 e f Functions 1 b Packages 4 g Triggers 2 e Aliases 5 mon 11 E 12 H i pw LI ALA lra ra lra l 2 ila1I mMITIDIAZ2IMITI IIAA di Refresh Nickriames New Structured type The New
30. receives the parameters specified on the CALL GENERAL WITH NULLS In addition to the parameters on the CALL statement specified under GENERAL another argument is passed to the procedure JAVA This means that the procedure will use a parameter passing convention that conforms to the Java language and SQL Routines specification SQL In addition to the parameters on the CALL statement the following arguments are passed to the procedure A null indicator for each parameter on the CALL statement The SQLSTATE to be returned to DB2 The qualified name of the procedure The specific name of the procedure The SQL diagnostic string to be returned to DB2 Parameter CCSID Specifies the encoding scheme to use for all string data passed into and out of the proc edure ASCII Specifies that string data is encoded in the database code page UNICODE Specifies that string data is encoded in Unicode Program type Specifies whether the procedure expects parameters in the style of a main routine or a sub routine The default is SUB Language This mandatory clause is used to specify the language interface convention to which the proced ure body is written C This means the database manager will call the procedure as if it were a C procedure JAVA This means the database manager will call the procedure as a method in a J ava class NEW SAVEPOINT LEVEL Specifies whether or not this procedure establishes a new savepoint level for savepo int n
31. that mapping between data of original source file and column of target table is done correctiy Data Import Wizard Data Import Wizard a Column Mapping Data Preview Set field mappinas to specify the correspondence between fields in the source and Ki Please check your data before importing Ki a DE F SD v a E LI FIRST NAME LAST NAME EMAIL PHONE NU Import Wizard E Import Wizard Steven King SKING 515 123 4 O Neena Kochhar NKOCHHAR 515 123 4 m Lex DeHaan LDEHAAN 515 123 4 E Alavandar Eih sell AHINAM san 472 ac 0 le A y E 6 Select the Import Mode and then click the Execute button Data Import Wizard ka Summary We ve gathered all the information the wizard needs to import your data Please click the Execute button to begin the import process Import Mode 9 Append Add records to the destination table Import Wizard Update Update record in destination matching with record from source Append Update If record exists in destination update it Otherwise add it Delete Delete records in destination that match records in source 010 Copy Delete all records in destination table replace with source contents Append New Add records only if there is no recored in destination table Next gt gt gt Execute O a Add Add a record to target table Copy Delete data in target table and adds record to original source file If you set key for row i
32. the File gt Save buttons after editing o Preview Report 4G HGU4 0 4 8 me Qa agag ns a mn ea EI ETE TABLE INFORMATION E Select a report at left tree Click the Preview button lt 51 gt SOLGate for DB2 User Manual http www antwiz com 3 6 2 SOLGate for DB2 User Manual Export Scripts If you use Script Export you can extract DDL scripts information of several database objects and send them to SQL Query Editor easily Select Tools gt Reports gt Export Scripts on the main menu or click the En button pa sw Export Scripts administrator SAMPLE30 Col e k Scripts fable og ZE View Scripts F Select Al Script options Constraint Scripts Index Scripts Tables 38 Source Report O CL SCHED Source Report C DEPARTMENT a Trigger Info O TESTTEST Alias Scripts EMP PHOTO Sequence Scripts lt C EMP RESUME Distinction type Scripts E PROJECT Structured Type Scripts O PROJACT Nickname Scripts C EMPPROJACT Parr S Be pe E CREATE TABLE PROJECT 8 PROJNO CHARACTER 6 NOT NULL 9 PROJNAME VARCHAR 24 NOT NULL DEFAULT 4 10 DEPTNO CHARACTER 3 NOT NULL 7 11 RESPEMP CHARACTER 6 NOT NULL 12 PRSTAFF DECIMAL 5 2 13 PRSTDATE DATE 14 PRENDATE DATE 15 MAJPROJ CHARACTER 6 IR lt DIBO amp Export Cancel administrator SAMPLE30 Count 1 Select an object type to export
33. the context menu by clicking right mouse button after selecting User Directory in the left tree Parent Group Root gt User Directory gt ene id MES DML is saved to SQL History group basicall y SQL can move from SQL history list to the other group through drag amp drop If you double click grid the selected SQL transfers to SQL Query Editor You can save to dat file or open SQL history through the Open File or the Save File button 21 gt http www antwiz com SOLGate for DB2 User Manual e Reuse SQL Check the Reuse checkbox in SQL history which is included to User Directory group and its sub groups Input wanted characters in Name EE ERR base shea Aa du Brot OTO dD p SQL History l S O User Directory E testes SELECT FROM ADMINISTRATOR CATALOG a If you click the shortcut key lt Ctrl M gt in SQL Query Editor SQL will be called Select one in reuse list E Run GI stp b ke Ip A AE a a la D E ES im Ole o BA SGBOX PeartaealSOrez a 0 4 Hna 00 Dual testest lt 22 gt http www antwiz com SOLGate for DB2 User Manual SQL which is saved in SQL history will be inputted automatically gt Run gt gt o al EI stp Ps Ip A EJES Ella Pi OS bIDDtSEBDnXZ utaABQFFABG 4 Oo W sqi x E 00 gt SELECT FROM ADMINISTRATOR EMPLOYEE P
34. to create index 5 Select columns to use as index at Table Columns list and then select the Y i Add All or the gt Add to move to right list 6 Check SQL by clicking the View SQL button 7 Click the OK button x Unique If ON table name is specified UNIQUE prevents the table from containing two or more rows wit h the same value of the index key The uniqueness is enforced at the end of the SQL statement that up dates rows or inserts new rows x Cluster Specifies that the index is the clustering index of the table x Allow reverse scans Specifies that an index can support both forward and reverse scans that is scanni ng of the index in the order that was defined at index creation time and scanning in the opposite orde r Properties Specify properties such as Tablespace Allocated Space Page Split Statistics and so on 37 http www antwiz com SOLGate for DB2 User Manual 3 5 5 New Procedures Creates procedures Select Create gt Procedures on the main menu C3 New Procedure administrator SAMPLE30 so ns Schema ADMINISTRATOR y Name test Type EA Comment _ General Parameter SQL Statement Statement General Specific name External Name Parameter Style Lanquage DB2GENERAL l Ic Parameter CCSID Program type DEFAULT w DEFAULT Execution Info Autonomous DBInfo Called on null input Deterministic Commit on return Y Ex
35. 0 2003 10 10 000030 000050 2005 04 05 1979 08 17 AN 000070 2005 09 30 2003 09 14 000090 2000 08 15 000100 000110 won Oh um bh wu HM ra 2000 06 19 1988 05 16 000120 11 000130 DELORES 5 1993 12 05 2001 07 28 14444 4 gt wo ot fc pata 1 28672 1Sec 2 1 Insert Row ANSI DOS CAP NUM ENG Trial period will be expired in 28 day s e Object Scripts If you click the right mouse button after selecting Table or View objects you can use Object Scripts menu helping SQL creation 2 SOLGate fol DB2 SOL Que ditor administrator O SAMPLE30 5QL1 File Edit View Query Create Tools Window Help Default Layout w administrator SAMPLEsON A W 2 SQL Query Editor EA S Alia a Era a e A bw m W AA AAA bol ab meats 4 09 Bs Dx 2 Gen 42 808 a Tables 38 A bo SELECT FROM ADMINISTRATOR EMPLOYEE a fa A Open Description Window F4 a Object Scripts p G Paste SELECT FROM AAAA E a dE New Table l Paste SELECT Fields FROM AAAA a Add p doe Replace SELECT FROM AAAA ED a Edit Replace SELECT Fields FROM AAAA i Drop Truncate NewTab SELECT FROMAAAA Ei mmm Se _ OP New Tab SELECT Fields FROM AAAA am 3978 sat New Window SELECT FROM AAAA B01 3476 New Window SELECT Fields FROMAAAA PON _ C01 4738 2 SSES
36. 17 E ADMINISTRATOR 27 3 Tables 38 a 15 Views 22 4c 8 Constraints 34 5 7 Indexes 31 6 1 p Procedures 4 d ah F Functions 1 a E W Packages 4 lo e Triggers 2 mx die DE A 11 c E MIN sik New Trigger 12 F Dee A Edit 13 c Sequen ni M aa Structur Script Report gt n Nicknam ag Filter E HR LR iio Refresh n New Trigger The New Trigger window pops up Edit The Edit Trigger window of the selected trigger pops up Drop Drop the selected trigger lt 80 gt Drop Drop the selected object Script Report Show script reports of the selected function View Edit Privilege Filter Filter and sort lists by using the Filter window Trigger Inquire and edit granted privileges to the selected object SOLGate for DB2 User Manual Inquires trigger list of the selected owner and detail information and performs overall features about trigger such as creation modification deletion and so on eg Object administrator 2SAMPLE30 7 System Schemas 7 MIN SALARY ge Aliases la Sequences Distinction types Structured type 3 Nicknames Q SYSTOOLS administrator SAMPLE30 Object Explorer administrator O SAMPLE30 DO_NOT_DEL_SALES Creation Date 2011 04 12 PM 4 17 36 pop LA N Name 1 Trigger Name DO NOT D I SALES ADMINISTRATOR 2 Trigger
37. 2011 03 24 13 01 01 592003 Statistics time 2011 04 08 00 00 00 000000 4 TBSCAN SARG 7 58 Column count 5 3 Table Index a Row count 65 WIDTH 27 PAGES 1 DISTINCT N Tahlaenara_nama LICEDCDACE1 gt 4 m Ka Data Execution plan Message History If you select the Object Name after checking the View execution plan Object information about that object will be displayed at the right lt 30 gt http www antwiz com 3 4 7 3 4 3 Query Description SOLGate for DB2 User Manual You can preview columns by using Query Description feature before executing a query manually if it is created as subquery type joined query of several tables or took long time to execute 1 Create SQL in SQL Query Editor oe 2 Select Query gt Query Description lt Shift F4 gt on the main menu or press the A button 3 Column information of SQL will be shown konba C3 Query Description administrator SAMPLE30 T T PROJNO CHAR ACTNO CHAR ACSTAFF NUMBER 0 ACSTDATE CHAR ACENDATE CHAR PROJNO_1 CHAR PROJNAME CHAR DEPTNO CHAR RESPEMP CHAR PRSTAFF NUMBER PRSTDATE CHAR PRENDATE CHAR MAJPROJ CHAR A Lo Php WN Fe o administrator SAMPLE30 Add to User SQL Saves created SQL in SQL Query Editor to User Directory of SQL HISTORY lt F8 gt Select Query gt Add to User SQL lt Shift F8 gt on the main menu sal Add to User SQL mon BE ERE BR Root SQL
38. 3 Not Null N LOCATION CHARACTER 16 N aa e i E EMPPROJACT E EMP PHOTO EMP RESUME E EXPLAIN ARGUMENT m y epi GF a EI EXPLAIN OBJECT Comment Save E EXPLAIN OPERATOR E EXPLAIN PREDICATE E EXPLAIN STATEMENT E EXPLAIN STREAM E INVENTORY E IN_TRAY A 1 ANGIIAGF administrator SAMPLE30 e Tab Information of Table Column You can inquire create delete and print out column information of the selected table Also you can inquire and modify column comment and table comment Index You can inquire add modify delete print out and rebuild index information of the selected table Constraint You can inquire add modify delete print out enable and disable constraint of the selected table Trigger You can inquire add modify delete print out enable and disable trigger information of the sel ected table Data You can inquire add modify delete and print out all data of the selected table Scripts You can inquire DDL by wanting option and save it Privilege You can inquire add modify delete and print out granted privilege to the selected table Partitions You can inquire partition information if the selected table is partition table Properties You can inquire creation option of the selected table and storage information lt 72 gt http www antwiz com SOLGate for DB2 User Manual e Context Menu of Table P
39. ATE as eo ea fe m View SQL OK Cancel administrator SAMPLE30 e Primary Key Primary Key 1 Input Name of constraint 2 Select a schema and then select a table to create primary key 3 Select the Primary Key button in Constraint tab 4 Select columns to use as primary key at left list in Key Column tab and then select the ited Add All or the gt Add to move to right list 5 Check SQL by clicking the View SQL button 6 Click the OK button e Check 1 Input Name of constraint 2 Select a schema and then select a table to create check 3 Select the Check button in Constraint tab 4 Input Check Constraint Condition in editor 5 Check SQL by clicking the View SQL button 6 Click the OK button e Unique 1 Input Name of constraint 2 Select a schema and then select a table to create unique 3 Select the Unique button in Constraint tab lt 35 gt http www antwiz com SOLGate for DB2 User Manual 4 Select columns to use as unique key at left list in Key Column tab and then select the GA Add All or the gt Add to move to right list 5 Check SQL by clicking the View SQL button 6 Click the OK button e Foreign Key Input Name of constraint Select a schema and then select a table to create foreign key Select the Foreign Key button in Constraint tab Select a table to refer in schema and table at right PY NY pP Select sour
40. Clipboard select from member Paste in the code select from member 3 2 7 Select All Select all texts in editor Select Edit gt Select All lt Ctrl A gt on the main menu lt 12 gt http www antwiz com SOLGate for DB2 User Manual 3 2 8 Clear All Clear all texts in editor Select Edit gt Clear All on the main menu 3 2 9 Find and Replace Find or Replace contents in editor Select Edit gt Find and Replace on the main menu 3 2 10 Bookmark Add Move or Delete bookmark in editor Select Edit gt Bookmark on the main menu Toggle Bookmark Add bookmark Goto Bookmark Move to bookmark Clear All Bookmarks Delete all bookmarks 3 2 11 Goto line Move to inputted line at the Goto line dialog Select Edit gt Goto line lt Ctrl G gt on the main menu Goto line Enter line number IR Cancel 3 2 12 Word Wrap The text wraps around if it is longer than horizontal area of the editor Select Edit gt Word Wrap on the main menu 3 2 13 Show Space Tab Carriage Return Space Tab and Carriage Return will be shown among texts in editor Select Edit gt Show Space Tab Carriage Return on the main menu 3 2 14 Toggle Folding lt 13 gt http www antwiz com 3 2 15 3 2 16 3 2 17 3 2 18 3 2 19 3 2 20 SQLGate for DB2 User Manual If the folding is set marks will be displayed t
41. Delphi Description Schema F4 VB C k E Editor Options ASP i re PHP E PK Column Datat NULL EMPNO EE N do f FIRSTNME VARCHAR N Options save va reg a sam a rro ae ny 3 2 21 Description Schema Open Description Schema window about the keyword where a cursor is located in editor Select Edit gt Description Schema lt F4 gt on the main menu 15 gt http www antwiz com 3 2 22 Editor Options Set options of editor Select Edit gt Editor Options on the main menu e General Set general options General Font _ Highight Code Template Keyword Template Auto Completion Options E Overwrite Mode V Auto Indent Mode Y Backspace Unindents E Undo Group F Redo Group V Keep caret in text E Double dick to select a line E Fix line height of row F Persistent Block E Word Break on Right Margin E Pin Column While Caret Moves Y Overwrite Block 4 Show caret in read only mode E Copy to dipboard as RTF format Y Enable Column Selection Y Hide Selection no focus Y Dynamic Hide no focus V Enable text drag n drop E Collapse Empty Line E Keep Trailing Blanks E Word Wrap Variable Horizontal Scrollbar Float Markers Y Undo After Saving E Disable Block Selection E Highlight the current Line Hide Cursor At a Keystroke E Scroll to last line E Keep Block Selection Mode E Smart Caret E Uninde
42. E30 o B Schema ADMINISTRATOR y Name test Comment _ General Attribute Method Q Super type Using Reference Schema ADMINISTRATOR y Type PROJECT y La taw La Law IT DATA Options Inline Lenath Instantiable Ca 0 Lei With Function Access Not Final View SQL OK Cancel administrator SAMPLE30 Specify a Schema Input a name of creating structured type at Name Select a Type Specify each items at the General tab Check SQL by clicking the View SQL button Click the OK button General SuperType Specifies a supertype A user defined structured type may include zero or more attributes A structured type may be a subtype allowing attributes to be inherited from a supertype Ref Using Defines the built in data type used as the representation underlying data type for the referenc e type of this structured type and all its subtypes Inline Length This option indicates the maximum size in bytes of a structured type column instance to s tore inline with the rest of the values in the row of a table Instances of a structured type or its subtypes that are larger than the specified inline length are stored separately from the base table row similar to t he way that LOB values are handled Instantiable Determines whether an instance of the structured type can be created Implications of not instantiable structured types are no constructor function is
43. LETE Specifies that the triggered action associated with the trigger is to be executed whenever a DELE TE operation is applied to the subject table or subject view UPDATE Specifies that the triggered action associated with the trigger is to be executed whenever an UPD ATE operation is applied to the subject table or subject view subject to the columns specified or implied OLD ROWS AS Specifies a correlation name which identifies the row state prior to the triggering SQL oper ation NEW ROWS AS Specifies a correlation name which identifies the row state as modified by the triggering S QL operation and by any SET statement in a BEFORE trigger that has already executed OLD TABLE AS Specifies a temporary table name which identifies the set of affected rows prior to the tri ggering SQL operation NEW_TABLE AS Specifies a temporary table name which identifies the affected rows as modified by the tr iggering SQL operation and by any SET statement in a BEFORE trigger that has already executed FOR EACH ROW Specifies that the triggered action is to be applied once for each row of the subject tabl e or subject view that is affected by the triggering SQL operation FOR EACH STATEMENT Specifies that the triggered action is to be applied only once for the whole statem ent e When Clause Input conditions to execute trigger e Body Describe procedure to call when executes trigger New Alias Creates
44. NO PROJE SELECT EMPLOYEE 13 EMPPROJACT EMPNO FROM EMPLOYEE 14 EMPPROJACT PROJNO PRO A WHERE EMPLOYEE ENPNME BROJEGT PROJNO 2 E 4 EMPPROJACT 5 6 AND EMPLOYEE LASTNAME EMPPROJACT EMP lt 67 gt http www antwiz com 4 3 Run SubQuery block You can find subquery in complex SQL automatically and execute it 0 SQL pra P D sto Dek las a aan i EE EISES MAA DO SQLI x a P Un SELECT FROM PROJACT A SELECT FROM PROJECT B WHERE B RESPEMP gt 100 B WHERE A PROJNO B PROJNO Nou gt Run subquery in the line where cursor is located in SQL Query Editor Subquery can be executed even it is not in block SOLGate for DB2 User Manual Font color of subquery in editor is changed automatically and the Run SubQuery block button will be activated Select Query gt Run SubQuery block Alt F5 gt on the main menu or press the l button 4 4 Multi Tab Several tabs can be added in SQL Query Editor so you don t need t open several SQL Query Editor Press shortcut key lt Ctri Alt N gt or click the MET button piar ery Editor administrator SAMPLE30 SOL1 n gt stop b k e lala a B amp GoOx 2 44 2a 6 50 E sad SQL Query Editor administi sal SQL Query Editor administrator SAMPLE30 SQL2 gt Run brn D gt Q so Dek Slelam aan amp 09 G A E OO R180x 2 desen d den EI Qi ISQLi x E SQL1 sQu2 x
45. NVC EN AN Y adonde cio 49 WO eo al fi ti sl E A e 50 A a e e a a a e ke ae pe a en 50 A kis p ae ja nt daar n e pe e on at ki a cect it E at fo n pk pos ana a p 52 o t l e ent l kk a e a kk oke ao tak e akt 53 APOT ELI A L A E 54 o on e e ye rl eny te n at tl tp lak pt ak la n ea tait Die a 56 VV WA Le UE 62 Mn PP O ess n a e ol a e gee eeu ts 62 EM V Le EE 62 E a ina l fan ii aa ie a a ate kl rt e fo at aa te ian e kif ae a e a ra pe NO ka ka fan ond Ne e pk ape Oak fon va 62 PAGS EE dee 62 Kl BEE 63 FUE If ke dore sela taa a leti A oui kt kretien pil il e eee e ot pon kn d epe E en eke iii kitan 63 A VAO e akt fk e a e a at nt EPA te e a E ke bk ae Ati ee a pe eke 63 Vo iq EE 63 A YE EE 64 SUL OUERT EDOR trop rt batt fe at a ki ak at si ko ek et kk eat se ok ek en e be e 65 A o A pt a a ES An 65 SPECII DACKO ei biet Color OF Oates 66 RA sO NO CI a ae kali abi aw yo e ud E tl kap rra 68 Oil EE 68 Table Auto Completion ou eee ccssccssssecsseecssseecssseecssseecssecesaeeessaecesseecesssecesseecesseecesseeseeaeeceeaeecesaeecss 69 Sege COMPE EE 70 EECHER EE 70 SEENEN 70 ESC UOT EE 71 as E 72 Ee E 72 MINOW O A E A A A niu Fe PW AE ME MA 74 CONSTA Moraira 76 o A A e po e A AE E I Oye pe e KO Date na ket ki e 77 POCA US oe 78 FUNCION urraca 79 e nm e o o 80 JAN NI A OP li tr ae tat e CO E A 81 E8 E Ee 82 lt 3 gt http www antwiz com id Rp e mi kel pa GE d ep E NNNNNNNNN N E SOLGate for DB2 U
46. PLOYEES table was specified in SQLI editor r sel SQL Query Editor administrator SAMPLE30 SQL3 GLEJ pran P b stop Dd elt Aa A Gl EB LAN sa L POZ AA NE dow BI LE tel az x Es 00 A SELECT 2 EMPLOYEE EMPNO 3 EMPLOYEE FIRSTNME 4 EMPLOYEE LASTNAME 5 PROJECT PROJNO 6 7 8 EMPPROJACT EMPNO sa SQL Query Editor administrator SAMPLE30 SQL1 FROM ban P gt stp D Ws k 4 EA a a PROJECT gt 9 EMPLOYEE Be Bat OX 4 10 EMPPROJACT sar x Ge 11 WHERE a E SELECT WW 12 EMPLOYEE EMPNO PROJECT PROJNO AND 13 EMPPROJACT EMPNO EMPLOYEE EMPNO A S FR j 14 EMPPROJACT PROJNO PROJECT PROJNO a PROJECT ao 4 EMPPROJACT 5 WHERE ENPNME PROJECT PROJNO 6 AND LASTNAME EMPPROJACT EN 3 When you specify the background color of an object which is not specified background color in the previous editor in other editor the background color will not removed in the previous editor 66 gt http www antwiz com SOLGate for DB2 User Manual SQL Query Editor administrator SAMPLE30 SQL1 else i prun pit Gsv bik Soa Bb Eb m AEOO DHBLXBDXPL ss BA 0 SELECT 2 EMPNO 3 FIRSTNME 4 LASTNAME 5 PROJECT PROJNO 6 EMPPROJACT EMPNO SQL Query Editor administrator SAMPLE3O SQL1 7 FROM 8 PROJECT k n A b Esto b Me lik si ai 16 EMPPROJACT 11 WHERE 3 12 EMP
47. QL Executes SQL where cursor is located only in SQL Query Editor Select Query gt Run Current SQL lt Ctrl F5 gt lt Ctrl Enter gt on the main menu or press the EI button QL Query Editor administrator 9SAMPLE30 SQL1 pra P D Qsoo Dele aaa ab DEDO IIBABOX 2 ra RAS OQFFABABS w Air gt soi x Es 00 gt SELECT FROM ADMINISTRATOR EMPLOYEE A 2 SELECT FROM ADMINISTRATOR DEPARTMENT a 5 HIREDATE JOB 1995 01 01 PRES 2003 10 10 MANAGER 2005 04 05 MANAGER 1979 08 17 MANAGER 2003 09 14 MANAGER 2005 09 30 MANAGER 2000 08 15 MANAGER 2000 06 19 MANAGER LUCCHESSI 1988 05 16 SALESREP O CONNELL 1993 12 05 CLERK QUINTANA l 2001 07 28 ANALYST a O sl O T a gt H Vertical Grid 28672 rows 5 x Run SQL SELECT FROM ADMINISTRATOR EMPLOYEE a in the line where cursor is located 27 gt http www antwiz com SOLGate for DB2 User Manual 3 4 4 Run SubQuery block Run subquery in the line where cursor is located in SQL Query Editor Subquery can be executed even it is not in block Font color of subquery in editor is changed automatically Also the Run SubQuery block lt AIt F5 gt button is activated Select Query gt Run SubQuery block on the main menu or press the lb button or lt AIt F5 gt key SOL Query Editor administrator SAMPLE30 SOL1 p
48. SOLGate for DB2 User Manual DB2 Database Development amp Management Tool SQLGate for DB2 User Manual 2011 Antwiz Inc All rights reserved Contents of this manual and SQLGate for DB2 program are protected by copyright law and the protection act of computer program m Technical Support Customer Support http www antwiz com antwiz antwiz com l http www antwiz com SOLGate for DB2 User Manual SQLGate for DB2 Table of Contents 1 Introduction of SOLGate for D I 5 1 1 To A 5 1 2 TECC le e en E 5 2 POC SOS CII NO asentaron ka a bate pate t batido 6 2 1 MAJ OP SOC CIC AU ON ere 6 2 2 Operation Environment 7 3 Main Menu 8 3 1 o e ak d D se 8 3 1 1 VIE 8 3 1 2 NEW COMECON ENEE 8 3 1 3 Biere 9 3 1 4 SC OPiS Ce YAN EE 9 3 1 5 io EE 9 3 1 6 E E EEA EAA 10 3 1 7 A a 10 3 1 8 A A ri e ne e A ao op ene e 10 3 1 9 A 10 O NC ee q 10 IPP akte kantite E bak ae ak ie bat E kar e ke tt an e a l e kat nt an ets bi 10 Sd ANE pia 11 3 1 12 AR 11 3 1 13 GO S ANVI MM OO EE 11 3 1 14 FINE P F W NO MW EE 11 3 1 15 PO EA 11 E DOU PONE OPA o PP ee lk e SO et eat 11 3 1 17 ta cotorra das 11 iZ Ee 12 3 2 1 A SPES EPA RAN YN 12 3 2 2 A Onn te Te nee ai are 12 3 2 3 SS An 12 3 2 4 SOD EE 12 3 2 5 tools ooo docto codos 12 3 2 6 Paste from the Ode siii a da 12 3 2 7 E EE 12 3 2 8 Clear All cccccccccccccssssscccccccccecccccccccccaeeeecccccccuausceeecececuuuusececcce
49. Schema ADMINISTRATOR Tables 38 3 Remarks 18 Views 22 4 Definer ADMINISTRATOR Le Constraints 34 5 TableName STAFF Indexes 31 6 Table Schema ADMINISTRATOR pd Procedures 4 7 Time before EI Functions 1 8 Event Delete 88 Packages 4 9 Granularity Row Triggers 2 10 Valid Valid La DO NOT DEL SALES 11 Qualifier ADMINISTRATOR Function Path SYSIBM SYSFUN SYSPROC SYSIBMADM ADMINISTRATOR Created 2011 04 12 PM 4 17 36 Es JLo es http www antwiz com 5 8 Script Report Show script reports of the selected trigger Filter Filter and sort lists by using the Filter window Alias creation modification deletion and so on fo Object Explorer administrator SAMPLE30 bo af e Y DEPT Creation Date 2011 03 24 PM 1 01 00 Last Modified 2011 03 24 PM 1 01 00 Object Com _ Column Data Properties Scripts ooo E W administrator SAMPLE30 ae e L System Schemas gt Columns Position Table Name Table Schema Reference Table Name Reference Table ADMINISTRATOR 1 DEPINO o DEPT ADMINISTRATOR DEPARTMENT ADMINISTRATOR S Ki Tabies 38 2 DEPTNAME 1 DEPT ADMINISTRATOR DEPARTMENT ADMINISTRATOR 9 E Views 22 3 MGRNO 2 DEPT ADMINISTRATOR DEPARTMENT ADMINISTRATOR Lei Constraints 34 4 ADMRDEPT 3 DEPT ADMINISTRATOR DEPARTMENT ADMINISTRATOR H A Indexes 31 5 LOCATION 4 D r ADMINISTRATOR DEPARTMENT ADMINISTRATOR Procedu
50. Specifies whether the function is considered safe to run in the same process as other routines THREADSAFE or not NOT THREADSAFE Data access level NO SQL Indicates that the function cannot execute any SQL statements CONTAINS SQL Indicates that SQL statements that neither read nor modify SQL data can be executed by the function READS SQL DATA Indicates that SQL statements that do not modify SQL data can be executed by the function Cardinality This optional clause provides an estimate of the expected number of rows to be returned by t he function for optimization purposes Scratchpad length This optional clause may be used to specify whether a scratchpad is to be provided for an external function A scratchpad provides a means for the function to save state from one call to the next e Return Tab Return Type Specifies the data type of the output Cast From This form of the RETURNS clause is used to return a different data type to the invoking state 42 http www antwiz com SOLGate for DB2 User Manual ment from the data type that was returned by the function code As Locator Specifies that a locator to the value of the parameter is passed to the function instead of the actual value 3 5 7 New Trigger Creates triggers on the database Select Create gt Trigger on the main menu ED New Trigger administrator 9 SAMPLE30 ola Schema ADMINISTRATOR a Name
51. a PU SELECT FROM ADMINIS gt SELECT FROM ADMINISTRATOR EMPPROJACT a ACTNO EMPTIME EMSTDATE EMENDATE 0 5 2002 01 01 2002 07 01 1 2002 01 01 2003 02 01 1 2002 01 01 2002 03 15 0 5 2002 03 15 2002 04 15 0 5 2002 03 15 2002 10 15 0 5 2002 04 15 2002 10 15 0 5 2002 10 15 2003 01 01 1 2002 02 15 2002 09 15 1 2002 09 15 2003 01 01 1 2NNJ Ni1 N1 2002 12 01 000110 000120 000130 000140 EENEG i 3 3 318 3 8 8 3 8 8 8 8 1 28672 OSec Select 1 73 OSec 2 1 Insert Row ANSI DOS 68 gt http www antwiz com If you add or modify SQL in SQL Query Editor symbol will be displayed on the tab pRun P b Qstoo Dd ks ik aaa aan mail iDAXBADnX ZAN iz IR MIO Y OM ADMINISTRATOR EMPPROJACT a If you modify SQL after saving SQL you can check modified line in gutter b s P gt Qsoo dD elt las a ab gt OOl aAsaenx Z 7 a 4 amp BR ISQL2 sql x G SELECT FROM PROJACT A SELECT FROM PROJECT B WHERE B RESPEMP gt 100 B WHERE A PROJNO B PROJNO pRun nit Qsto Ds RA E A Al l blek amp OOl a 68nDXxX P2 7 feo B SQL2 sql x 3 SELECT FROM PROJECT B WHERE B RESPEMP gt 100 B WHERE A PROJNO B PROJNO 4 5 Table Auto Completion Table lists are displayed after From Into Update keyword automatically You can create SQL statement
52. a is in UTF 8 and that graphic data is in UCS 2 Parameter Style This clause is used to specify the conventions used for passing parameters to and returni ng the value from functions DB2GENERAL Used to specify the conventions for passing parameters to and returning the value from external functions that are defined as a method in a J ava class SQL Used to specify the conventions for passing parameters to and returning the value from external functions that conform to C language calling and linkage conventions methods exposed by OLE automation objects or public static methods of a NET object lt 41 gt http www antwiz com SOLGate for DB2 User Manual Transform Group Indicates the transform group to be used for user defined structured type transformation s when invoking the function A transform is required if the function definition includes a user defined struc tured type as either a parameter or returns data type lf this clause is not specified the default group na me DB2 FUNCTION is used Allow Parallel Specifies whether or not for a single reference to the function the invocation of the funct lon is to be parallelized Called on null input If CALLED ON NULL INPUT is specified then regardless of whether any arguments are null the user defined function is called It can return a null value or a normal non null value Inherit special registers This optional clause specifies that updatable special registers in the f
53. aliases for table view alias or other aliases Select Create Alias on the main menu ED New Alias administrator SAMPLE30 oe x Schema ADMINISTRATOR y Name Comment Target object Schema ADMINISTRATOR Name View SQL OK Cancel administrator SAMPLE30 1 Specify a Schema lt 44 gt http www antwiz com ST gen JK n Input a name of creating alia at Name Specify a schema and table of target object Check SQL by clicking the View SQL button Click the OK button to apply the alias 3 5 9 New Sequences Defines sequences on the application program server Select Create gt Sequences on the main menu ED New Sequence administrator SAMPLE30 a LJ Schema ADMINISTRATOR y Name Source Data Type INTEGER Z Values Initial Maximum Value 1 at Increment By Minimum Value 1 at at Options 20 No Cache View SQL OK Cancel administrator SAMPLE30 SQLGate for DB2 User Manual 1 Specify a Schema 2 Input a name of creating sequence at Name 3 Specify a Source Data Type 4 Input the Value option 5 Select Options 6 Check SQL by clicking the View SQL button 7 Click the OK button xX Source Data Type Data type used to sequence value e Value Initial Input initial value of sequence Minimum Value Input minimum value of sequence Maximum Value Input ma
54. ames and effects OLD SAVEPOINT LEVEL is the default behavior DBinfo Specifies whether specific information known by DB2 is passed to the procedure when it is invoked as an additional invocation time argument DBINFO or not NO DBINFO NO DBINFO is the default Deterministic This clause specifies whether the procedure always returns the same results for given argum ent values DETERMINISTIC or whether the procedure depends on some state values that affect the results NOT DETERMINISTIC External Action Specifies whether the procedure takes some action that changes the state of an object n ot managed by the database manager EXTERNAL ACTION or not NO EXTERNAL ACTION The default is EX TERNAL ACTION Fenced This clause specifies whether the procedure is considered safe to run in the database manager o perating environment s process or address space NOT FENCED or not FENCED Threadsafe Specifies whether the procedure is considered safe to run in the same process as other routin es THREADSAFE or not NOT THREADSAFE Data access level Indicates whether the procedure issues any SQL statements and if so what type NO SQL Indicates that the procedure cannot execute any SQL statements CONTAINS SQL Indicates that SQL statements that neither read nor modify SQL data can be executed by the procedure READS SQL DATA Indicates that some SQL statements that do not modify SQL data can be included in the procedure lt 39 gt h
55. antwiz com 1 3 7 4 1 5 Group Area gion 01 DEPARTMENTS w A Departments table that shows details of depart N D DEPARTMENT ID NUMBER 4 DEPARTMENT NAME VARCHAR2 30 MANAGER ID NUMBER 6 LOCATION_ID NUMBER 4 Ties up table text objects to one group Object in group area moves with group area when moves group area Modify Name Color Comment after popping up Edit Region window by double clicking AAN Add sentence of simple contents on Canvas Open the text box edit window by double clicking and then edit contents in text box Text Box Contents Double click the text box to change contents Setup Print Select File gt Print on the main menu to open the Setup Print window Print Page lt 92 gt SQLGate for DB2 User Manual http www antwiz com 1 5 1 7 6 SQLGate for DB2 User Manual All Pages Print out whole model Selected Pages Print the selected model in model Model Select only a model that you want to print out with mouse in status pressing the lt Ctrl gt key Page Setup Page setup window shows up Print Print model through printer Page Setup ED Printer Setting eg Model Page Size Direction E A4 210x297 mm 8 26x11 7 inches y Vertical O Landscape Pages Horizontal 4 OI OI Vertical 4 OK Choose a printer
56. bject Panel on the main menu or clicking the EL button on the toolbar Select needed table or view in object panel and drag amp drop at query builder by using mouse Check columns to display in SQL Leo JLo ks Query Builder administrator SAMPLE30 gt Run O AA S Builer sad SQL Select y Query 1 WW B Query 1 2 ADMINISTRATOR EMPLOYEE _ ADMINISTRATOR PROJACT _ x EMPNO String PROJNO String FIRSTNME String MIDINIT String ACSTAFF Float ACSTDATE Date WORKDEPT String ACENDATE Date PHONENO String ADMINISTRATOR PROJECT _ x PROJNO String a PROJNAME String DEPTNO String RESPEMP String PRSTAFF Float PRSTDATE Date a 4 II Criteria et Grouping ana Sorang E All of the following are met administrator SAMPLE30 e join Select a line between objects that join was established and then click the Property on the context menu oo ks Query Builder administrator SAMPLE30 gt Run O AA Query 1 L ADMINISTRATOR EMPLOYEE ADMINISTRATOR PROJACT ix EMPNO String PROJNO String FIRSTNME String MIDINIT String ACSTAFF Float ACSTDATE Date WORKDEPT String ACENDATE Date PHONENO String Insert point Delete link Property PRSTAFF Float PRSTDATE Date 4 II Criteria Selection Grouping criteria Sorting All ofthe following a
57. ce field at left list and reference filed at right list x Just data types of both fields should be same 6 Click the L button to move to down list 7 Repeat 5 6 steps 8 Check SQL by clicking the View SQL button 9 Click the OK button 3 5 4 New Index Creates index of table or cluster on database Select Create gt Index on the main menu CH New Index administrator 2 SAMPLE30 so 1 Jee Schema ADMINISTRATOR Name test Comment _ General Properties gt E Unique C Allow reverse scans C Cluster Schema ADMINISTRATOR y Table EMPLOYEE y Table Columns Index Columns Column Name Data Type Null a ASC Column Name Data Type Null EMPNO CHARACTER 6 NOT NULL gt il MIDINIT CHARACTER 1 NOT NUL FIRSTNME VARCHAR 12 NOT NULL LASTNAME VARCHAR 15 NOT NULL WORKDEPT CHARACTER 3 PHONENO CHARACTER 4 HIREDATE DATE JOB CHARACTER 8 EDLEVEL SMALLINT NOT NULL SEX CHARACTER 1 BIRTHDATE DATE Column Name Data Type SALARY DECIMAL 9 2 BONUS DECIMAL 9 2 conan DECIMAL fo m won Oh um bh WH ra H E va O ok View SQL OK Cancel administrator SAMPLE30 o General Tab lt 36 gt http www antwiz com SOLGate for DB2 User Manual Specify the Schema Input a name of creating index at Name Move to General tab Select Table Ze A A Select a Schema and Table
58. cessed 109 Delete 0 Skipped 0 You can check importing result and error record 3 6 5 Options Sets options of program Select Tools gt Options on the main menu or click the Eh button e General fi Options ma General DB2 Configuration z a SQL Query Editor r Convert SQL Going Object Explorer Grid i Query Shortcuts Automatic Updates V Enable Automatic Updates lt Select Language English e Start Window SQL Query Editor 7 Object Explorer T Display connection info in Task Bar awy Ip 1 Cem Themes Select theme of program Default and Dream are provided Automatic Updates When checked it checks update automatically whenever program executes Select Language Select a language to use at program Korean English Japanese Chinese German French and Spanish are provided 56 gt http www antwiz com SOLGate for DB2 User Manual Start Window Select a window to be executed when starts program Default is SQL Query Editor SQL Query Editor Execute SQL Query Editor automatically when starts program Object Explorer Execute Object Explorer automatically when starts program Display connection info in Task Bar Display session ID on task bar O DB2 Configuration You can set rows count of query results execution plan table user and auto commit Options General Fetch Records Opti
59. dE button or lt F6 gt key after creating Select query statement Navigation is displayed at grid of Data tab as modification mode and you can insert modify delete by selecting grid gt Run sQLi x On Oh mm bh QU NN n Select 29 PI li E saj SQL Query Editor administrator SAMPLE30 SQL1 Le JLo es Stop Re i Sal a 4 a E Ca del la s t L xX yo gt Z Los IO A e 00 bk SELECT FROM ADMINISTRATOR EMP PHOTO a HOES 000130 000130 000140 000140 000150 000150 000190 000190 bitmap gif bitmap gif bitmap gif bitmap gif Ik DN pp cua Data Execution plan 1 8 OSec 2 1 Insert Row ANSIDOS Vertical Grid 8 rows select http www antwiz com SQLGate for DB2 User Manual 3 4 6 Execution plan of current SQL Executes Execution Plan for created SQL in SQL Query Editor Results will be shown as tree type after executing Execution Plan command which saves execution plan to plan table Plan table must exist to execute Execution Plan Click Query gt Execution plan of current SQL F7 gt on the main menu or the del button Results can be checked at Execution plan tab a Ea M View property Object properties Operation How applied Total cost Object schema ADMINISTRATOR 3 RETURN a Object name PROJACT R J 5 HSJOIN JOIN 15 19 Object type TA f 2 TBSCAN 7 60 gt Create time
60. dow on the main menu you can close the current window Close All Windows If you select File gt Close All Windows on the main menu you can close all windows Print Preview Select File gt Print Preview on the main menu to open preview window Print Select File gt Print on the main menu to print contents on SQL Query Editor Setup Print If you select File gt Printer Setting on the main menu Print Setting Page Setting will be shown You can set printer and page setting Exit Select File Exit on the main menu to close program after disconnecting connected session lt 11 gt http www antwiz com SOLGate for DB2 User Manual 3 2 Edit 3 2 1 Undo Cancel the just previous task Select Edit gt Undo lt Ctrl Z gt on the main menu 3 2 2 Redo Re execute canceled task Select Edit gt Redo lt Shift Ctrl Z gt on the main menu 3 2 3 Cut Cut off selected contents Select Edit gt Cut lt Ctrl X gt on the main menu 3 2 4 Copy Copy selected contents to clipboard Select Edit gt Copy lt Ctrl C gt on the main menu 3 2 5 Paste Paste contents in clipboard Select Edit gt Paste lt Ctrl V gt on the main menu 3 2 6 Paste from the code Paste contents by extracting SQL statement in contents of clipboard Select Edit gt Paste from the code on the main menu or press the shortcut key lt Shift Ctrl V gt Ex
61. e as Federated Server or add separately for Federated System Above configuration can be set by using DB2 Control Center Or register Wrapper by using Federated Server with CREATE WRAPPER statement Wrapper has mechanism that Federated Server interacts with specific type of data source Specify a Schema Input a name of creating alias at Name Select Server Information Select Remote Object Information Check SQL by clicking the View SQL button Click the OK button Y PE BF SEI e n lt 49 gt http www antwiz com 3 6 3 6 1 SQLGate for DB2 User Manual Tools Reports If you use Object Report Tool you can design and print out various reports by yourself You can handle report writing easily and fast because report can be designed and printed out in template format Object report template files are stored in Report folder in fr3 file format You can select report type at left panel and set printout options at right panel in object report tool Select Tools gt Reports gt Object Reports on the main menu or click the p n button Object Reports administrator SAMPLE30 1 Jee E AJ Gy 2 Report Font Tahoma Schema ADMINISTRATOR fable and Columns E select Table Scripts View Tables 38 View and Columns AAAA View Scripts O act Constraints act2 Constraint Scripts F C CATALOG Indexes C o SCHED Index Info j C CUSTOMER Index Scripts C DEPARTMENT
62. eating function at Name Select a Type Specify each items at the General tab Check SQL by clicking the View SQL button Click the OK button gt er W mE P e Function Type lt 40 gt http www antwiz com SOLGate for DB2 User Manual External Scalar The CREATE FUNCTION External Scalar statement is used to register a user defined exter nal scalar function at the current server A scalar function returns a single value each time it is invoked an d is in general valid wherever an SQL expression is valid External Table The CREATE FUNCTION External Table statement is used to register a user defined external table function at the current server A table function can be used in the FROM clause of a SELECT and returns a table to the SELECT by returni ng one row at a time SQL Scalar SQL Table SQL Row The CREATE FUNCTION SQL scalar table or row statement is used to defi ne a user defined SQL scalar table or row function A scalar function returns a single value each time it is invoked and is generally valid wherever an SQL expression is valid A table function can be used in a FRO M clause and returns a table A row function can be used as a transform function and returns a row Sourced Template The CREATE FUNCTION Sourced or Template statement is used to Register a user defined function based on another existing scalar or aggregate function at the current server Register a function template with an application s
63. ege Filter Function SOLGate for DB2 User Manual Inquire and edit granted privileges to the selected object Filter and sort lists by using the Filter window Inquires function list of the selected owner and detail information and performs overall features about procedure such as creation modification deletion and so on Object e E Packages Co administrator SAMPLE30 C System Schemas B ADMINISTRATOR Tables 38 Views 22 Le Constraints 34 Indexes 31 pa Procedures 4 F Functions 1 i L fw RESIGN EMPLOYEE Triggers Aliases f Sequences Distinction types 2 Structured type 1 7 Nicknames B rr 2 vum B sav g systoaus administrator SAMPLE30 Object Explorer administrator SAMPLE30 e y Es JLo zs RESIGN_EMPLOYEE Creation Date 2011 04 12 PM 4 33 33 Last Modified 2011 04 12 PM 4 33 33 e DH bie Name ADMINISTRATOR 104121 400 e n Date 2011 04 12 4 33 33 P ified 2011 04 12 4 33 33 tat Rebinding DROP SPECIFIC FUNCTION SQL116412163333400 CREATE FUNCTION resign employee number CHAR 6 RETURNS TABLE empno CHAR 6 salary DOUBLE dept CHAR 3 MODIFIES SQL DATA LANGUAGE SQL BEGIN ATOMIC employee from the EMPLOYEE table A useful extension to this functio II Le Tab Information of Function Scripts Display creation script of the selected object
64. erver that is designated as a federated server A functio n template is a partial function that contains no executable code The user creates it for the purpose of mapping it to a data source function o General Tab Specific Name Provides a unique name for the instance of the function that is being defined This specific name can be used when sourcing on this function dropping the function or commenting on the function External Name This clause indicates that the CREATE FUNCTION statement is being used to register a new function based on code written in an external programming language and adhering to the documented linka ge conventions and interface Language This mandatory clause is used to specify the language interface convention to which the user de fined function body is written C This means the database manager will call the user defined function as if it were a C function J AVA This means the database manager will call the user defined function as a method in a J ava class CLR This means the database manager will call the user defined function as a method in a NET class OLE This means the database manager will call the user defined function as if it were a method exposed by an OLE automation obj ect Parameter CCSID Specifies the encoding scheme to use for all string data passed into and out of the func tion ASCII Specifies that string data is encoded in the database code page UNICODE Specifies that character dat
65. euuusseeeccecuaueseeeececeescaauaeeceeseeuauauseeeeeeceuaaeess 13 3 2 9 ito e nn e 13 3 2 10 a 13 3 2 11 A ele a 13 3 2 12 TWOP OP VD EES 13 3 2 13 Show Space Tab Carriage Retum nn none nncnnnnnonns 13 Jl TOO 13 3 2 15 COMENTE BO GK ET 14 3 2 16 LA CON ug BIO EE 14 3 2 17 o a 14 3 2 18 Increase Nden ki o etni sates a ee a e ki n ayo L t Bo eks ano a 14 3 2 19 LOC O CAN tt n ont o ae 14 3 2 20 GONE AE SO kte ty l tasca 14 E ee e gel SENMENN A EE 15 A o o ee t de ea at ot ti ak pa ee ee kk ban re isk a pasi piti 16 3 3 AA A 19 3 3 1 A O kabes e 19 3 3 2 O Lo a E 20 EK OLAP n gel 23 lt 2 gt http www antwiz com GE E ES E E HO 00 ln UD UN A NRO Ct a sd ER 00 69 C9 0900 SAINI be be EE EENEG ES OWN kA P WINE mum um UI bb kbkbbbbb fs WUWU td UW W UW UW UW U U U U U U UW U U U U U U U U www w w ww www Ei Ei www ONDU INEA 00 ADUANA SOLGate for DB2 User Manual e e o e e o RO e la a a r a mai 24 EE DO ee 24 RUN EE 26 pao eege 27 A DIOCK EE 28 Run Current SQL amp MOI asesinados eta 29 Execution Dlan Or CUNT EAT SQL rin jip e Make aa kot keyi EEEE 30 ONE DE SE le Le EE 31 AUTO USO 31 el e Le 32 CCOO reto ett ar ki bote 33 Dl SWE TON E E in ti eta n Un kt e e e a a ee eee 33 VS 24 KSE A A 35 NEW o EE 36 NEW eene 38 NW FUNCION marti 40 NOW TUS Pornstar 43 A O recesses asa ak a ate kk e ki e a nies ene 44 NOW ne 45 NON DIS MON e ad 46 NEW SU UC AV D posando 46 NEW
66. gate com en support Technical Support 82 2 338 1876 5 http www antwiz com 2 1 SOLGate for DB2 User Manual Product Specification Major Specification e Unicode Support It supports Unicode Multilingual can be handled through it UTF8 UTF16 AL32UTF8 character sets are supported e User Friendly Interface It consists of editor grid and user friendly interface which are convenient and powerful features It satisfies user s needs by providing various styles and options to grid such as XP Float Office and so on e Easy and Fast Query Execution Users can create SQL code easily by using SQL Query Editor and check results of queries Moreover users can create faster and more accurate SQL code by using the auto completion feature eo Powerful Object Browser It helps inquiry creation modification and deletion for each user through object browser Users can inquire easily and quickly by linking with SQL Query Editor e Provision for Tool of Various Features Even beginners can operate various database management tasks easily by providing various tools such as Word Wrap Bookmark SQL Conversion Report Import Export Utility and so on e Report Tool to Print as Desired Types Users can design directly and print out data by using Report Designer and shorten creation time of various reports e ER Design Feature Users can create ERD entity relationship diagram by reversing relationship bet
67. generated for a non instantiable type a non instantiable type cannot be used as the type of a table or view a non instantiable type can be used as the type of a column only null values or instances of instantiable subtypes can be inserted into the column With Function Access Indicates that all methods of this type and its subtypes including methods created in the future can be accessed using functional notation Not Final Indicates that the structured type may be used as a supertype 47 gt http www antwiz com SOLGate for DB2 User Manual e Attribute 1 Move to the Attribute tab 2 When click the db button a row will be added 3 Input Name Type and so on click the OK button ED New Structured Type administrator SAMPLE30 koba Schema ADMINISTRATOR Generp Attribute Method t Name Type Length Prec Scale bitdata Logged Compact Type Sc Type Na 1 INTEGER 2 MN une el eil E I m e View SQL OK Cancel administrator SAMPLE30 e Method 1 Move to the Method tab 2 When click the T button Add Method window will be opened 3 Add Method babai Name Specific name Language c x _ Return Type Parameter Properties Self as result Data Type z FOR BIT DATA MB ak 4 La Ce administrator SAMPLE30 3 Specify Return Type Parameter
68. ges Y Tables Y Sequences V Aliases Y Distinction types Y Structured type V Nicknames Bring to front When checked Schema Browser will be shown on top lt 59 gt http www antwiz com SOLGate for DB2 User Manual e Grid You can change Display Null setting in grid and grid style a DB2 Confiquration Display NULL null a Font color of multiple line Convert SQL cid Tie Expresa i Query Shortcuts Grid Style Express sa New Style Edit Style Font Delete Style Drag a column header here to group by that column Date 1924 03 03 1940 12 05 1979 07 26 1980 06 06 1920 05 15 2006 10 25 1981 01 01 2006 10 01 Display NULL Display NULL value in grid Set background Font color of multiple line Set background color when text in grid has carriage return value x Only one line of data is displayed in grid so you cannot know how many lines in grid Grid Type Change grid type Standard Flat Ultra Flat Office Grid Style Change grid style You can change grid style manually e Query Shortcuts You can set specific commands as shortcut keys lt Alt 1 10 gt i DB2 Configuration SQL Query Editor Convert SQL i Please input amp Var in case of variables lt 1 Select from Var
69. hen checked SQL will be saved MAX SAVE COUNT OF SQL HISTORY Unlimited Save all without limitation Count Save as much as inputted number STORE POINT OF SQL HISTORY After Execution Save only succeeded query commands Before Execution Save all query commands ES Select View gt SQL History lt F8 gt on the main menu or click the sa 1 button 20 http www antwiz com BEIB REE E BY Root SQL History o C 5 User Directory 5 Dual es base Spe du pubert 1 Connection Info administrator SAMPLE30 d af E SQL SELECT FROM ADMINISTRATOR EMPLOYEE a SELECT FROM ADMINISTRATOR EMPLOYEE a SELECT FROM ADMINISTRATOR EMPP SELECT FROM ADMINISTRATOR EMPPROJACT a 5 3 20 5 3 2011 8 SELECT FROM ADMINISTRATOR DEPARTMENT a 5 3 2011 7 SELECT FROM ADMINISTRATOR EXPLAIN_STATEMENT a 15 3 2011 7 SELECT FROM ADMINISTRATOR EMP_PHOTO a 5 3 2011 7 SELECT FROM PROJECT B WHERE B RESPEMP gt 100 5 3 2011 7 SELECT FROM ADMINISTRATOR AAAA a 5 2 2011 5 SELECT FROM PROJACT A SELECT FROM PROJECT B WHERE B RESPEMP gt 100 5 2 2011 5 opoopo mm SELECT FROM ADMINISTRATOR EMPLOYEE a SELECT FROM ADMINISTRATOR EMPM 5 2 2011 4 m SQLGate for DB2 User Manual You can add folder by selecting New Group on
70. ing query execution plan results will be displayed at bottom pran P p Oz Dd bes le L E la 909 0B ABOXL2 o s DP Un SELECT FROM PROJACT A elt SELECT FROM PROJECT B WHERE B RESPEMP gt 100 2 B WHERE A PROJNO B PROJNO Rs Y View property Operation EH RETURN 5 HSJOIN 2 TBSCAN ADMINISTRATOR PROJACT TA 2011 03 24 13 01 01 592003 2011 04 08 00 00 00 000000 N USERSPACE1 If you select a row of execution plan results object and index information are displayed at right of the screen lt 71 gt http www antwiz com 5 1 SQLGate for DB2 User Manual Object Explorer Table It provides overall features about tables that inquires creates modifies and deletes table lists by owner and detail information of each table Select View gt Object Explorer on the main menu or click the El button Object Explorer administrator SAMPLE30 koHa 3s gt Y DEPARTMENT Creation Date 2011 03 24 2 1 01 00 Last Modified 2011 04 12 2M 10 13 24 Ge _ Column Index Constraint Trigger Data Scripts Privileges Properties Reference Referenced By Wi administrator SAMPLE30 gt GE A e E System Schemas 3 B ADMINISTRATOR Tables 38 E AAAA PK Column Data Type NULL Default Identity Specification Generated bit PK1 DEPTNO CHARACTER 3 Not Null N PK2 DEPTNAME VARCHAR 36 Not Null N MGRNO CHARACTER 6 N ADMRDEPT CHARACTER
71. int Eg P A Edit e Pi e Drop e i Script Report gt ES Py Filter SP Refresh a Pi vannen CS miatimiaioa4i4i4i4ininials a New Constraint The New Constraint window pops up Edit The Edit Constraint window of the selected constraint pops up Drop Drop the selected constraint Script Report Show reports of the selected constraint Filter Filter and sort lists by using the Filter window 76 Column You can inquire column information of constraint and print out it Referenced By You can add referenced by objects of the selected constraint Information You can inquire constraint information such as owner type table name and so on Scripts You can inquire creation script save to file export to SQL Query Editor and so on http www antwiz com 5 4 Index SOLGate for DB2 User Manual Inquires index list by owner and detail information of index and performs overall features about index such as creation modification deletion and so on po Object Explorer administrator SAMPLE30 Fe za gt Y CUST_NAME_XMLIDX Creation Date 2011 03 24 PM 1 01 10 Ges Column W administrator SAMPLE30 a System Schemas 3 B ADMINISTRATOR Tables 38 Views 22 d 4 Column Position Order Data Type NULL Table Name Table Schema INFO 1 Ascending XML Yes CUSTOMER ADMINISTRATOR po le Constraints 34 Indexe
72. l http www antwiz com 3 8 3 8 1 3 9 2 3 8 3 SQLGate for DB2 User Manual Help Help Opens help file Select Help gt Help lt F1 gt on the main menu Activation Authenticates program as formal commodity Select Help gt Activation on the main menu Activation r Activation SQLGate for DB2 License Key ai w Paste Buy Online Activation Close You are connected to the Internet If you input the license key of 16 digits and click the Activation button program authentication will be finished Update Check Checks latest update information Currently using version and latest version are displayed Select Help gt Update Check on the main menu Update Check O update Check SQLGate for DB2 A new update is available Current version 1 0 3 6 Latest version 1 1 0 2 Latest update date 2011 05 03 2 2 05 15 Automatic update expired date Trial period will be expired in 28 day s If you dick Update Now for update SQLGate will be restart and update lt b3 gt http www antwiz com 3 8 4 About SOLGate Inquires program information Select Help gt About SQLGate on the main menu 64 SQL Gate Update Version 1 1 0 2 Trial period will be expired in 28 day s This software is protected by the protection act of computer program copyright law universal copyright convention and other
73. n Column Mapping step you can select mode among Update Append Update and Delete mode Update tem will be activated as follows 55 gt http www antwiz com SOLGate for DB2 User Manual aa RE ve Data Import Wizard m m Data Import Wizard a Column Mapping Summary Set field mappings to specify the correspondence between fields in the source and We ve gathered all the information the wizard needs to import your data Please click destination files the Execute button to begin the import process a a Import Mode E 9 Append Add records to the destination table Iv Oh AE Update Update record in destination matching with record from source lv 7 Append Update If record exists in destination update it Otherwise iw add it iv Delete Delete records in destination that match records in source 010 lw 5 Copy Delete all records in destination table replace with source contents HI Append New Add records only if there is no recored in destination table Update Update corresponding record from original Append Update Update if record exists in target table or adds if not Delete Delete record if data of original source file exists in target table Append New Add records only if there is no record in target table 7 Importing result will be shown Data Import Wizard x An error has occurred during the data import process Co Added 1 Error 54 Update 0 Pro
74. nt Align SOLGate for DB2 User Manual Number of Undo 1000 Tab ode ea crc Tab Size 4 Collapse Level 1 we JI oe j cms Restore Defaults e Font Set font options Character Set DEFAULT CHARSET Z If the letter is not visible change the character set Gutter Right Margin 2 Visible Width 30 Y Visible Position 80 coo ses A oi E 7 Line Number m 3E sa English TE EAH HE zMnvixf yAwooa T rk e ti ng Vi t mw dal ll pycckui an bet D un gt un Sample SQL CREATE OR REPLACE PROCEDURE SP ADDRESS as_empnol in VARCHAR2 as_empno2 in VARCHAR2 as_result out VARCHAR2 2 15 RE Soo x If specific text is broken in editor change the character set lt 16 gt http www antwiz com e Highlight Set highlight options ditor General Font_ Highight Code Template Keyword Template Auto Completion Element Bold E Italic Font Style Font Color Font Color MN Green ell anc de English D As AE z nvi k yAwooa T rk e ti a ak pyccKu H amni Sample SQL a CREATE OR REPLACE PROCEDURE SP ADDRESS as empnol in VARCHAR2 as empno2 in VARCHAR2 as result out VARCHAR2 LI IS Restore Defaults SOLGate for DB2 User Manual You can change font style font color and so on after selecting one of elements in Element
75. ntwiz com SOLGate for DB2 User Manual Specify background color of text This feature marks background color of the table which is willing to find in SQL Query Editor If you select Find All when you find wanted words in the editor you can find all same words and specify background color to found words In addition you can specify different background color to each word 1 If you click the E button after selecting the table which is willing to see that will be changed into wanted background color ei sat SQL Query Editor administrator SAMPLE30 SQL3 Col e Jee brn P gt Oz ik 4 Amaai ab Ay m DA SI DS OD zap Edel BI LISS d gt sat3 x f 00 A SELECT 2 EMPNO 3 FIRSTNME 4 LASTNAME 5 PROJECT PROJNO 6 EMPPROJACT EMPNO 7 FROM 8 PROJECT 9 A 10 EMPPROJAC 11 WHERE 12 EMPUOVEE empno PROJECT PROJNO AND 13 EMPPROJACT EMPNO EMPROYVEB empno AND 14 EMPPROJACT PROJNO PROJECT PROJNO 0 0 0 2 6 Insert Row ANSI DOS Vertical Griz 2 If you specify background color of the same object in another editor the specified background color of the previous editor will be removed According to the below image you can see that the background color of the EMPLOYEES table in SQL1 edit or is removed when the background color of the EMPLOYEES table which is the same object is specified in SQL2 editor even though the background color of the EM
76. o collapse or expand editing contents at the left of the editor If you click the editing contents will be collapsed and click the they will be expand again Select Edit gt Toggle Folding on the main menu Comment Block Make selected text in editor into comments Marked contents as comment will not be executed Select Edit gt Comment Block lt Ctrl gt on the main menu Uncomment Block Remove blocked comments of selected texts in editor Select Edit gt Uncomment Block lt Shift Ctrl gt on the main menu Convert to Convert selected texts in editor into upper case lower case upper lower case or convert only first character into upper case Select Edit gt Convert to on the main menu Convert to gt Upper Case Ctrl U j F Increase Indent LowerCase Ctrl Decrease Indent Shift Tab Aa Change Case se Convert SQL gt Abc Capitalize Ctri I Pr n n n Increase Indent Indent one line in editor Shortcut key lt Tab gt Select Edit gt Increase Indent on the main menu Decrease Indent Remove the indentation of one line in editor Select Edit gt Decrease Indent lt Shift Tab gt on the main menu Convert SQL Convert texts in editor into Delphi Visual Basic ASP PHP J SP code and copy to clipboard Select Edit gt Convert SQL on the main menu lt 14 gt http www antwiz com SOLGate for DB2 User Manual sd Convert SQL gt
77. obj ect on Canvas after that you select a table from table list Displays column list of table primary key data type logical field name and so on Table Object Menu Change Table Display Options l __ Reflected in All Tables Y View Table Logical Name Column 1 Field Name E Column 2 Data Type w Column 3 None y Click the right mouse button at table object and then select the Change Table Display Options on the context menu Reflected in All Tables Reflect changed option value of table display to all tables View Table Logical Name Display with logical name of table ColumniI Column n Select items to display at table object OK Reflect changed option value of table display to table Delete Object Align Refresh Change Table Display Options Reference Table Parent tables Referenced By Child tables Delete Object Delete the selected object Align Align the table to up down left and right horizontal vertical Refresh Refresh the table Reference table Parent table Referrer Child table If there is a constraint on the table the parent or the child table of the selected table will be called automatically lt 90 gt http www antwiz com deg erg SOLGate for DB2 User Manual Relationship 220 2 10 DEPT_MGR_FK If you select one among 1 1 Non Identifying Relationship yi Lin Non Identifying Relationship
78. oi x fm 00 SELECT FROM ADMINISTRATOR EMPLOYEE A SELECT FROM ADMINISTRATOR DEPARTMENT a E 5 HIREDATE JOB 1995 01 01 PRES 2003 10 10 MANAGER 2005 04 05 MANAGER 1979 08 17 MANAGER 2003 09 14 MANAGER SPENSER 2005 09 30 MANAGER SPENSER 2000 08 15 MANAGER SPENSER 2000 06 19 MANAGER LUCCHESSI 1988 05 16 SALESREP O CONNELL 1993 12 05 CLERK 1 DELORES m QUINTANA 2001 07 28 ANALYST 144 kal 4 o no foot cx WW N Data Select 1 28672 OSec 1 1 Insert Row ANSI DOS Vertical Grid 28672 rows 5 oon Oh um bh ou HM ra jollon o gt 5 If you select text in SQL Query Editor only selected text will be executed If there is no selected block all SQLs will be executed n SQL Query Editor ad nistrator SAMPLESC SOL1 brn P D so pf aaa aan pEOO kDACHBDX PA4 2IAsEPQFFABS Air gt squit x fk 00 ab SELECT FROM ADMINISTRATOR EMPLOYEE A gt SELECT FROM ADMINISTRATOR DEPARTMENT a SPIFFY COMPUTER SERVICE DIV PLANNING INFORMATION CENTER DEVELOPMENT CENTER MANUFACTURING SYSTEMS INFORMATION CENTER BRANCH OFFICE F2 SOFTWARE SUPPORT BRANCH OFFICE F2 BRANCH OFFICE F2 If you click the E button you can see horizontal grid lt 26 gt http www antwiz com SOLGate for DB2 User Manual 3 4 3 Run Current S
79. olumn Tab Creates table by inputting column information to create through row grid Select an owner Schema of table Input a Table name Input Comment of table Input a name at Column Name in row grid Select a data type of column at Data Type Input size of column at Size input box q O CPE A O A E Check Primary Key in case of primary key If you check Primary Key Not NULL will be checked automatical ly 8 Input default value at Default input box 9 Input comment of field at Comment input box 10 Repeat above steps as much as to add rows 11 Check SQL by clicking the View SQL button 12 Table will be created after clicking the OK button x Addable input boxes of 10 are displayed as default and if you press the lt gt key input box will be adde d e Options Tab You can specify options such as Storage Data Capture Compression Logging and so on lt 33 gt http www antwiz com SOLGate for DB2 User Manual 3 5 2 New View Creates new view on database To create view CREATE VIEW privilege is necessary Select Create gt View on the main menu C3 New View administrator SAMPLE30 o B Name TEST Schema ADMINISTRATOR w Comment _ Scripts View Information ta E Alim validates 1 SELECT FROM ADMINISTRATOR EMPLOYEE A View SQL OK Cancel administrator SAMPLE30 Input a Name Select a Schema to own Input SQL to use as view in
80. on Tab Count of Startup 1 wF v Show Grid at Startup Display SQL Hint on Tab Query Editor Options Set query editor options If you click the Options button new window will be sh own to set SQL Align Option Set SQL alignment options If you click the Options button new window will be sho wn to set Current SQL Separator Set which you will use as separator for SQL semicolon or carriage return AUTO SAVE SQL HISTORY Set options whether to save or not If you check this SQL will be saved automa tically MAX SAVE COUNT OF SQL HISTORY Unlimited Save all without limitation Count Save only inputted count STORE POINT OF SQL HISTORY After Execution Save only succeeded queries Before Execution Save succeeded or failed queries Tab Count of Startup lt is number of tabs which are created automatically by executing SQL Query Editor Show Grid at Startup When checked grid will be displayed when executes SQL Query Editor If it is not ch ecked only editor will be shown and grid will be shown after executing query Display SQL Hint on Tab When checked SQL will be shown as hint when mouse over tab 58 gt http www antwiz com SOLGate for DB2 User Manual e Object Explorer Select objects to display in Object Explorer Object Panel 4 Bring to front Visible Objects IV Tables Y Views Y Constraints V Indexes IV Procedures Y Functions V Packa
81. ons DB2 Configuration SQL Query Editor O Fetch All Records Convert SQL Partially fetch d Object Explorer oe ee ee Grid Fetch Rows Query Shortcuts E r J Query Options Date Format vyyy MM dd AMPM h mm ss Y Keep Connection Reconnection Check Time 30 Seconds Apply OK Cancel Fetch Records Options Fetch All Records Display all records in grid after inquiry Partially fetch records Fetch partially after inquiry Fetch rows means records count to fetch partially Query Options Date Format Set date type to display in grid Execution plan Table Name It is table name to use execution plan in SQL Query Editor If you use table of other user you should input user name or table name Keep Connection When checked it keeps connection of session continuously Reconnection Check Time Set automatic reconnection time 57 gt http www antwiz com SOLGate for DB2 User Manual e SQL QUERY EDITOR Sets number of start tab editor options alignment options history save options and so on at O Unlimited Options a General DB2 Configuration Juer y Editor Options SQL Query Editor Options Convert SQL Object Explorer Grid SQL Align Options Query Shortcuts ke Current SQL Separator 9 Semicolon Carriage Return Y Auto Save SQL History Max Save Count of SQL History k Store point of SQL History After Execution O Before Executi
82. ord or not e Use Unicode Use Unicode mode when connect Date Format Date type format E Color Select color by session when you connect to several DB2s ES 2 Test Connection Execute connection test to inputted information kee Test Connection x If you add a database in Configuration Support Program of DB2 the database will be shown in New C onnection window as alias x In addition when more than one DB2 version of DB2 client are installed if you try to connect to the d atabase you must execute Default DB2 and database client interface select wizard and select the cop y of default DB2 Disconnect Disconnects to currently connected DB2 server Select File gt Disconnect on the main menu File Edit View Query Create New ki New Connection Wa Disconnect Disconnect All L Reconnect Disconnect All Disconnects to currently all connected DB2 servers Select File gt Disconnect All on the main menu File Edit View Query Create New LA New Connection a Disconnect Disconnect All w Reconnect Reconnect Reconnects to currently connected DB2 server Select File gt Reconnect on the main menu lt 9 gt http www antwiz com 3 1 0 ES es 3 1 8 3 1 9 3 l 10 SQLGate for DB2 User Manual Fie Edit View Query Create New LA New Connection A3 Disconnect Disconnect All w Reconnect gt Open cti o Open
83. orms overall features about type such as creation modification deletion and so on d Object Explorer administrator SAMPLE30 1 e ez gt Y A1 Creation Date 2011 04 05 PM 4 40 33 Last Modified 2011 04 05 PM 4 40 33 Object Comment Properties Scripts W administrator 2SAMPLE30 v a System Schemas a Name Value o A ADMINISTRATOR Type ID 32733 Tables 38 2 Schema ADMINISTRATOR gt 25 Views 22 3 Name Al 83 Constraints 34 4 Remarks Indexes 31 5 Source Schema SYSIBM Procedures 4 1 6 Source Name INTEGER Functions 1 7 Definer ADMINISTRATOR Packages 4 8 Length 4 en riggers 2 9 Scale 0 88 Aliases 5 a 10 Code page 0 la Sequences 1 11 Created 2011 04 05 PM 4 40 33 Distinction types 3 lt 12 Data Type Array 13 Array Length 30 y TEST 4 TESTTEST Structured type Nicknames E 2 HR o B num d soi o H sysTOOLs administrator SAMPLE30 Tab Information of Distinct type Properties You can inquire properties list of the selected Distinct type Scripts You can inquire the creation script save to file and send to SQL Query Editor Context Menu of Distinct type Object Explorer administrator 2 SAMPLE30 P Al Object Comment W administrator SAMPLE30 System Schemas 3 ADMINISTRATOR j Tables 38 Views 22 Le Constraints 34 Indexes 31 pal Procedures 4 F Functions 1 Packages 4 Trigge
84. ove Blank and Separator of keyword Line break Set location of line to change Align amp Indent Set alignment of Keyword Operator AS Keyword Comment AND OR and so on Comma Set blank before after comma Parenthesis Set blank before after parenthesis Operator Set blank before after operator lt 23 gt http www antwiz com 3 4 3 4 1 Query Create SQL SQLGate for DB2 User Manual If you select File gt New gt SQL Query Editor lt Ctrl N gt on the main menu or click the 24 Jbutton SQL Query Editor window will be shown e SQL QUERY EDITOR Tab You can use up to 20 If you use tab You can use SQL Query Editor and grid in each tab separately If you click the right mouse button in tab context menu will be shown sal SQL Query Editor administrator S gt Run P Stop D Bes IG po s t sQLi ve L Pa bi Close Ctri Alt D L Close All But This Close All Add Editor Ctrl Alt N Geck d Open File Ctrl 0 KR Save As S a LH Save ctri s H 2 print Preview Cti p lt 24 gt Close lt Ctri Alt D gt Close the tab Close All But This Close all tabs except the selected tab Close All Close all tabs Add Editor lt Ctri Alt N gt Add a tab Open File lt Ctrl 0 gt Open a file in the current tab Save As Save SQL in the current tab as another name Save Ctrl S gt Save SQL in the current tab Print P
85. ra P gt Qsoo b l f laa a ab SOA DA BD SIDD zi ir del DOF BAR G k Ee 00 A SELECT FROM PROJACT A ol SELECT FROM PROJECT B WHERE B RESPEMP gt 100 Noun SI B WHERE A PROJNO B PROJNO PROJNO PROJNAME PRENDATE MAJPROJ AD3111 PAYROLLPROGRAMMING D21 2 2002 01 01 2003 02 01 ap3110 AD3112 PERSONNEL PROGRAMMING D21 1 2002 01 01 2003 02 01 AD3110 AD3113 ACCOUNT PROGRAMMING D21 I 2 2002 01 01 2003 02 01 AD3110 MA2111 WLPROGRAMDESIGN Du 2 2002 01 01 1982 12 01 MA2110 MA2112 WLROBOT DESIGN D11 3 2002 01 01 1982 12 01 MA2110 MA2113 WLPRODCONTPROG D 000160 3l2002 02 15 1982 12 01 MA2110 OP2011 SCP SYSTEMS SUPPORT E21 1 2002 01 01 2003 02 01 oP2010 OP2012 APPLICATIONS SUPPORT E21 0330 1 2002 01 01 2003 02 01 oP2010 OP2013 DB DC SUPPORT E21 1 2002 01 01 2003 02 01 oP2010 RADA pata Select 1 9 OSec 4 15 Insert Row ANSIDOS Vertical Grid 9 rows sele 1 2 3 4 5 6 7 8 9 x Background color of subquery is changed automatically lt 28 gt http www antwiz com 3 4 5 Run Current SQL Modify SOLGate for DB2 User Manual You can run created SQL in SQL Query Editor and modify data in grid Run Current SQL and Modify is available to run for one Select query Primary Key column should be included to select script to run Select Query gt Run Current SQL and Modify on t he main menu or press the
86. re met st m lt 86 gt http www antwiz com SOLGate for DB2 User Manual Select Inner J oin Outer J oin gt Link properties join tables EMPLOYEES and JOBS JOB ID w JOB ID Include all from EMPLOYEES Include all from JOBS ETE e Set Where Select Group Sorting Condition You can add modify and delete Where selection group and sorting condition at the bottom of query builder e View SQL If you select the SQL tab you can check the created SQL o Run Press the shortcut key lt F5 gt or click the p button SQL is executed and you can check results lt 87 gt http www antwiz com SOLGate for DB2 User Manual ER Design You can create ERD entity relationship diagram by applying Reverse Engineering to relationship between tables in ER Design window It provides basic features which are necessary for ERD edit such as group area text box and so on also provides ERD print file saving and importing Select File gt New gt ER Design on the main menu or click the d button e Screen Composition e S ER Design administrator SAMPLE30 PROJACT m CHARACTER 6 4 RPP PROJNAME VARCHAR 24 DEPTNO CHARACTER 3 L5 PROJNO L ACTNO L RESPEMP CHARACTER 6 PRODUCT PRSTAFF DECIMAL 5 2 INVENTORY ACENDATE CUSTOMER PRSTDATE DATE H eps PRENDATE DATE MAJPROJ CHARACTER 6 CATALOG
87. res 4 f Functions 1 E Packages 4 A Triggers 2 Mi 43 Aliases 5 23 EMP li 23 EMPACT 25 EMP ACT 25 PROJ l Sequences Distinction types Structured type 0 Nicknames administrator SAMPLE30 Tab Information of Alias Column You can check the column of the selected alias Data You can add and modify data of the selected alias Properties You can check properties of the selected alias Scripts You can check creation script of the selected alias Context Menu of Alias Object Explorer administrator 2 SAMPLE30 e Y Object Com a W administrator SAMPLE30 a a System Schemas E H ADMINISTRATOR Tables 38 Views 22 Le Constraints 34 Indexes 31 lt Procedures 4 F Functions 1 Packages 4 A Triggers 2 i Aliases 5 ya N CH o na y amp PT 1 LA TP PI P B Pl Pl PB P a d i New Alias 25 E 2 Edit BE E mm Drop es d Script Report gt ia Sequ 2 Struc Refresh 3 Nicknames Hi Gi Di New Alias The New Alias window pops up Edit The Edit Alias window of the selected alias pops up Drop Drop the selected alias Script Report Show script reports of the selected alias 81 gt SOLGate for DB2 User Manual Inquires alias list of the selected owner and detail information and performs overall features about alias such as http www antwiz com SOLGate for DB2
88. review lt Ctrl P gt Preview contents to print in the current tab http www antwiz com SOLGate for DB2 User Manual e Context Menu of SQL QUERY EDITOR If you click the right mouse button in SQL Query Editor following context menu will be shown gt Execute gt Pa Execution plan N sa Query Description Shift F4 SQL Row Count F12 l SQL Align F9 Wl Undo Ctrl Z Redo Shift Ctrl Au Paste from the code gt Select All Ctrl a Clear All yo Find and Replace gt Bookmark gt Kl Goto line Ctrl G A5 Word Wrap T Show Space Tab Carriage Return El Togale Folding 5 Comment Block Ctrl CJ Uncomment Block Shift Ctrl Convert to gt ZE Increase Indent S Decrease Indent Shift Tab di Convert SQL gt Description Schema F4 Editor Options lt 25 gt Execute Execute SQL in editor Execute SQL of the selected text Query Description lt Shift F4 gt Field definition by analyzing SQL statement SQL Row Count lt F12 gt Count records where cursor is located at SQL Align lt F9 gt Align all SQLs in editor Undo lt Ctrl Z gt Cancel execution Redo lt Shift Ctrl Z gt Re execute Cut lt Ctrl X gt Cut off Copy lt Ctri C gt Copy Paste lt Ctrl V gt Paste Paste from the code Extract SQL from other program codes Select All lt Ctri A gt Select all Clear All Clear all Find and Replace Find Find Next Find All Find from fir
89. rint out trigger of the selected view Privilege You can inquire add delete and print out granted privilege to the selected view Reference You can inquire objects that the view uses Referenced By You can add referenced by objects of the selected view Scripts You can inquire creation script by wanting options and save it Context Menu of View Object Explorer administrator SAMPLE30 e Y TEST Object Car Ee Wi administrator SAMPLE30 sl a System Schemas E H ADMINISTRATOR Tables 38 Views 22 IIA SI vac fe New View 3 VA a Edit E vas 3 VDE ma Drop 3 vor Row Count J ven 2 New Alias H ver CH ven Object Reports GH ver Script Report di e e V i View Edit Privilege 3 VIE y Filter A VIE Refresh TJ VPHONE i a Sir Aimiuliriniec inrivwinim S New View The New View window pops up Alter The Alter View window of the selected view pops up Delete Drop the selected view Row Count Show record count of the selected view s data http www antwiz com SOLGate for DB2 User Manual New Alias Create an alias of the selected view Object Reports Script Report Show reports of the selected view View Edit Privilege Inquire and edit granted privileges to the selected view Filter Filter and sort lists by using the Filter window lt 75 gt http www antwiz com 5 3 Constraint SOLGate for DB2 User
90. rs 2 Aliases 5 fa Sequences 1 Distinction types 3 y DCH EC DEE DEE DECHE pa Boosavnswun ss i e KR ko E ja nu N ni iminialiairimitalrmainmizlinla di 1 TP New Distinction type 414 Edit Stru mm Drop Nicky BR Script Report H B NULLID e Filter sa Refresh bi Gi DI E New Distinct type The New Distinct type window pops up Edit The Edit Distinct type window of the selected Distinct type pops up Drop Drop the selected Distinct type Script Report Show script reports of the selected Distinct type Filter Filter and sort lists by using the Filter window lt 83 gt http www antwiz com 5 11 Structured type structured type such as creation modification deletion and so on Object Explorer administrator SAMPLE30 rc t fe ay ADDRESS_T Creation Date 2011 04 08 PM 2 27 07 Last Modified 2011 04 18 PM 6 36 03 Object Properties attribute Method Scripts oo W administrator SAMPLE30 a a System Schemas Name Value ADMINISTRATOR tl Schema ADMINISTRATOR Tables 38 2 Name ADDRESS_T Views 22 3 Remarks 83 Constraints 34 4 Definer ADMINISTRATOR Indexes 31 5 Created 2011 04 03 PM 2 27 07 Procedures 4 ES Sek Functions 1 7 Parent Name Packages 4 ell 8 Length 16 Triggers 2 9 Type can have instantiated Yes 185 Aliases 5 10 Methods can be invoked using function
91. ry Editor F 3 Alaa ea aa eaa HGO Object Panel X Run P Stop D Ka le sa EJE AA lo la la ey iOa 180x204 tak BQOFF Bo yon administrator SAMPLE30 e ST gt E HH w mon mmn W b BN Syetom Schemas DU SELECT FROM ADMINIST 3 B ADMINISTRATOR e 2 a SQL Query Editor LASTNAME VARCHAR WORKDEPT CHARACTER PHONENO CHARACTER 2 EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT 1 000010 CHRISTINE I HAAS ADO a 000020 MICHAEL L THOMPSON BO1 3 000030 SALLY A JEFFERSON CO1 4 000050 JOHN B 5 000060 IRVING F Data Grid 6 000070 EVA D E 7 000090 EILEEN Column Data Type 8 000100 THEODORE o SPENSER E21 A EMPNO CHE 9 000110 VINCENZO G LUCCHESSI ADO 2 A A 10 000120 SEAN O CONNELL ADO e EEN 11 000130 DELORES M QUINTANA c01 5 6 A Select 1 28672 1Sec 2 1 Insert Row ANSIDOS Technical Support PHONENO 3978 3476 4738 6789 6423 7831 5498 0972 3490 2167 4578 D ppt cu 4 T H Se HIREDATE JOB E 1995 01 01 PRES 2003 10 10 MANAC 2005 04 05 MANAC 1979 08 17 MANAC 2003 09 14 MANAC 2005 09 30 MANAC 2000 08 15 MANAC 2000 06 19 MANAC 1988 05 16 SALESF 1993 12 05 CLERK 2001 07 28 ANALY Vertical Gri DB2 Ver 9 7 CAP NUM ENG Trial period wil be expired in 28 day s If you have any questions about this software refer to this document or post questions on the Technical Forum in company s homepage http www sgl
92. s 31 lt l CUST_CID_XMLIDX i D CUST_NAME_XMLIDX l CUST PHONES XMLIDX El CUST PHONET XMLIDX El PK ACT E PK CATALOG fl PK CUSTOMER l PK DEPARTMENTO El PK DEPT l PK DEPTO El PK EMP PHOTO El PK EMP RESUME l PK INVENTORY El PK PRODUCT S 4 II li administrator SAMPLE30 Tab Information of Index Column You can check column information of the selected index Properties You can check creation property and using storage information Statistics You can see statistics of the selected index Privilege You can inquire and edit assigned privileges to the selected index Reference You can check objects that the selected index refers to Referenced By You can add referenced by objects of the selected index Scripts You can check creation information of the selected index Context Menu of Index Object Explorer administrator SAMPLE30 ey CUST Object 3 W administrator SAMPLE30 a System Schemas 3 B ADMINISTRATOR Tables 38 Views 22 Lei Constraints 34 Indexes 31 lt p CH o a l a V A m A cu el New Index A cu a Edit A CU a Drop A PK Rename F2 A PK Object Reports gt 1 ipt Ri t gt a PK Script Repor A PK A View Edit Privilege a PK a PK Y Filter Al PK Refresh El Pk PRODUCT m New Index The New Index window pops up Edit The Edit Index window of the selected index pops up
93. ser Manual ESV LV PO oa 83 o UA e CO e a baay kote DIANA k 84 Elei E 85 QUE BUM 86 EN DOI T 88 Ke E A A potre isi kanon ki ante do aba ae kipa son 90 Table Object Menu 90 FE ANONS MP EE 91 Change Relationship object EE 91 OUD ACA tota 92 TEE B E 92 e e H E 92 Fade Y L UI PIL tdo 93 FOO ONS ON J CT ONU miki E is ik tb fa e al ai a l kr a e ati ki n a ob en ta e es an 93 WV fre it past e A le o tapan Saati anis Pe e o ki dantan biten ot apt bani ant 94 lee NOYE Sarai 94 4 http www antwiz com 1 1 1 1 2 Introduction SOLGate for DB2 User Manual Introduction of SQLGate for DB2 SQLGate for DB2 is a powerful database management tool which provides you with an easy development and management for DB2 database applications It shortens development time and increases business efficiency of database manager and application developer by providing user friendly interface and various features You can create and execute SQL easily because code auto completion keyword auto completion and table auto completion feature is supported in SQL Query Editor Moreover you can change user interface into multi language version with just simple setting in options because this is created in 7 different languages E SQLGate for DB2 SQL Query Editor administrator SAMPLE30 SQL1 o File Edit View Query Create Tools Window Help Defaultlayout lel x om gt M N sa Que
94. sign ERD in easier way o Select Schema Tables of the selected database in select schema are displayed on table list o Filter Table List This is a feature to filter table list and it shows only table name which has inputted word in table list o Select All Check all checkboxes by table in table list e Table List Display only table which has filter word among tables of the selected schema in select schema If there is no filtering word all table lists of the selected database are displayed Add Table If you drag to Canvas or double click it after selecting a table in table list the selected table will be revered and putted on Canvas If you click the Add button checked tables in table list will be reversed and putted on Canvas e Map BO e TO e Show table on Canvas and currently working area briefly You can move working area of Canvas by clicking the mouse or dragging If you click the Zoom Out button contract Canvas If you click the Zoom In button expands Canvas lt 89 gt http www antwiz com 7 1 1 1 1 SQLGate for DB2 User Manual Table Object EMPLOYEES Employee Table EMPLOYEE_ID NUMBER 6 FIRST_NAME VARCHAR2 20 LAST_NAME VARCHAR2 25 EMAIL VARCHAR2 25 PHONE_NUMBER VARCHAR2 20 HIRE_DATE DATE 7 JOB ID VARCHAR2 10 SALARY NUMBER 8 2 COMMISSION_PCT NUMBER 2 2 MANAGER ID NUMBER 6 DEPARTMENT_ID NUMBER 4 PAY_TYPE VARCHAR2 5 This is an added
95. st Find the last Replace Bookmark Toggle bookmark Goto line lt Ctri G gt Move directly to the line by inputting line number Word Wrap Wrap around automatically Show Space Tab Carriage Return Show space tab and carriage return character Toggle Folding Turn on off folding feature Comment Block lt Ctrl gt Set comment Uncomment Block lt Shift Ctrl gt Release comment Convert to Convert to upper lower character Increase Indent Indent the line where cursor is located Decrease Indent lt Shift Tab gt Unindent line where cursor is located SQL CONVERT Convert into Delphi Visual Basic ASP PHP and J SP code Description Schema lt F4 gt Show Description window of keyword where cursor is located Editor Options Open editor options window http www antwiz com 3 4 2 SQLGate for DB2 User Manual Run SQL Executes created SQL in SQL Query Editor If several SQLs are executed it is separated with semicolon gt Select Query gt Run SQL on the main menu or press the button or lt F5 gt key In case of Select statement execution results are displayed as grid type in Data tab at the bottom In case of non Select statement execution results are displayed in Message History tab Click the a Jbutton or press lt ESC gt key to cancel execution pra P gt sto b s t aaa lab Pi Q rahaAGOx P 4A2NSA amp SORPF BAG ig s
96. st Connection lt 8 gt Password Password User Connection Account New Connection E Database Login SQLGate for DB2 E Es DB2 Client onec a avorite Description atabase ali User Password 2st Conneci Color Date F Database alias LI LU administrator SAMPLE30 SAMPLE30 administrate zg 2011 05 17 kenn DEFAL SAMPLE30 ES 2 antwiz SAMPLE26 SAMPLE26 antwiz 1 2011 05 16 DEFAL j 3 Administrator SAMPLE30 SAMPLE30 Administrat sal 2011 04 08 DEFAL User 4 administrator SAMPLE28 SAMPLE28 administratt sulla 2011 04 05 DEFAL dicos 5 antwiz SAMPLE SAMPLE lantwiz 1 2011 04 05 DEFAL 6 administrator SAMPLE SAMPLE administrat sulla 2011 04 05 n DEF AL Password eee pee lt wl Save Password V Use Unicode i lt Date Format DEFAULT Color 4 Test Connection View Favorities Only Show Password Connect Cancel Database alias Select the alias to connect to Use Unicode Use Unicode mode when connect Save Password Select whether to save password or not Color Select color by session when you connect to several DB2s Test Connection Execute connection test to inputted information http www antwiz com 3 1 3 3 1 4 3 1 5 SQLGate for DB2 User Manual e ODBC Tab L 22 Giep Opec ODBC Select the alias to connect to Goen W User Connection Account lt Password Password Ee Save Password Select whether to save passw
97. te that keyword in SQL Query Editor e Auto Completion Set whether to use Auto Completion feature Editor Options General Font Highlight code Template Keyword Template Auto Completion Auto Completion Features vi Tabli letion lt Ctrl H gt v Code Auto Completion lt Ctrl J gt Table Auto Completion CH Y Keyword Auto Completion lt Ctrl K gt Y Tables V Reuse SQL lt Ctrl M gt s vi View V Field Auto Completion lt gt V Index Y Procedure h jon Keyword Change Case Auto Completion Y Function Uppercase gt vi Triggers Auto Completion for Change Case of Object Name 7 Alias Uppercase V Sequence Delay vi Structured type Distinction type 0 1 Sec 1 5 Sec Y Nickname Code Auto Completion lt Ctrl j gt Display contents in code template after auto completion Keyword Auto Completion lt Ctrl K gt Keyword Auto Completion will be displayed if you type more than 3 characters Reuse SQL lt Ctri M gt Checked sentences are displayed among contents in SQL history Field Auto Completion lt gt Display field list auto completion next to table alias Keyword Change Case Auto Completion uishing upper lower character Auto Completion for Change Case of Object Name hing upper lower character Table Auto Completion Ctri H gt When a character is inputted after FROM INTO SET the object will be displayed
98. ternal action Y Inherit special registers F New savepoint level Data access level MODIFIES SQL DATA Dynamic result sets 0 View SQL OK Cancel administrator SAMPLE30 o General Tab Specify a Schema Input a name of creating procedure at Name Select a Type Specify each items at the General tab Check SQL by clicking the View SQL button Click the OK button P we PP Fr Specific Name Provides a unique name for the instance of the procedure that is being defined This speci fic name can be used when altering dropping or commenting on the procedure Parameter Style This clause is used to specify the conventions used for passing parameters to and returni ng the value from procedures DB2GENERAL This means that the procedure will use a parameter passing Convention that is defined for use with Java methods This can only be specified when LANGUAGE J AVA is used DB2SQL In addition to the parameters on the CALL statement the following arguments are passed to the procedure A vector containing a null indicator for each parameter on the CALL statement The SQLSTATE to be returned to DB2 The qualified name of the stored procedure The specific name of the stored procedure The SQL diagnostic string to be returned to DB2 GENERAL This means that the procedure will use a parameter passing mechanism by which the procedure lt 38 gt http www antwiz com SOLGate for DB2 User Manual
99. the Scripts tab Click the Validate SQL button to make sure that SQL is right oS a oe Ie Oe Move to the View Information tab and add column of view by using the Auto Add button or the Add b utton 6 Specify options such as Replace the view if exists WITH ROW MOVEMENT and With Check Option Replace the view if exists If view exists already overwrite it WITH ROW MOVEMENT Specify that updated rows move to the right default table even if the check constraint of the default table is violated With Check Option Specify the restriction that all rows inserted or updated through view must follow the definition of the view 7 Check SQL by clicking the View SQL button 8 Click the OK button lt 34 gt http www antwiz com SOLGate for DB2 User Manual 3 5 3 New Constraint Creates primary check unique or foreign key on database Select Create gt Constraint on the main menu CH New Constraint administrator SAMPLE30 se Jee Name test Schema ADMINISTRATOR D Table EMPLOYEE D Comment A Constraint O Primary Key Check Unique Foreign Key A Key Column Column Name Data Type NULL aa Column Name Data Type N 1 EMPNO CHARACTER 6 NOT NULL 1 MIDINIT CHARACTER 1 2 FIRSTNME VARCHAR 12 NOT NULL 3 4 WORKDEPT CHARACTER 3 E 5 PHONENO CHARACTER 4 6 z 8 9 Rav HIREDATE DATE JOB CHARACTER 8 EDLEVEL SMALLINT NOT NULL SEX CHARACTER 1 10 BIRTHDATE D
100. trl C Ctrl 0 Alt F5 F6 F7 Alt F7 Shift F4 Shift F8 F9 Ctrl H Shift Ctrl V F12 Ctrl M Keyword Auto Completion Ctrl K Field Auto Completion mm ES le http www antwiz com
101. ttp www antwiz com SOLGate for DB2 User Manual MODIFIES SQL DATA Indicates that the procedure can execute any SQL statement except statements that are not supported in procedures Dynamic Result Sets Indicates the estimated upper bound of returned result sets for the procedure eo Parameter Tab IN Identifies the parameter as an input parameter to the procedure Any changes made to the parameter within the procedure are not available to the calling SQL application when control is returned The default IS IN OUT Identifies the parameter as an output parameter for the procedure INOUT Identifies the parameter as both an input and output parameter for the procedure 3 5 6 New Function Creates functions Select Create gt Function on the main menu ED New Function administrator SAMPLE30 vn Il as Schema ADMINISTRATOR y Name Type External Scalar w Comment _ General Parameter Return General Specific name External Name Language Parameter Style c SQL Parameter CCSID Transform group DEFAULT w DEFAULT w Execution Info Allow parallel DBInfo Called on null input Deterministic Inherit special registers Y External action Final call v Fenced Y Threadsafe Data access level Cardinality NO SQL y 1 Scratchpad length 1 View SQL OK Cancel administrator SAMPLE30 Specify a Schema Input a name of cr
102. unction will inherit their initial values from the environment of the invoking statement For a function invoked in the se lect statement of a cursor the initial values are inherited from the environment when the cursor is opened Final Call This optional clause specifies whether a final call and a separate first call is to be made to a n external function It also controls when the scratchpad is re initialized If NO FINAL CALL is specified the n DB2 can only make three types of calls to the table function open fetch and close DBinfo This optional clause specifies whether certain specific information known to DB2 is to be passed to the function as an additional invocation time argument DBINFO or not NO DBINFO NO DBINFO is the def ault Deterministic This optional clause specifies whether the function always returns the same results for given argument values DETERMINISTIC or whether the function depends on some state values that affect the res ults NOT DETERMINISTIC That is a DETERMINISTIC function must always return the same table from succes sive invocations with identical inputs External action Specifies whether the function takes an action that changes the state of an object that t he database manager does not manage Fenced This clause specifies whether or not the function is considered safe to run in the database man ager operating environment s process or address space NOT FENCED or not FENCED Threadsafe
103. ween tables in ER Design window without installation of separate program lt 6 gt http www antwiz com Lido Operation Environment e Operation Environment All of Windows Family OSs Over Intel Pentium 4 Over 512MB Memory Over 50MB free hard disk space e Configuration Features of folders which are composed program are as follows Bin Composed of execution files and ini dat files HealthCheck Html files which are used at HealthCheck Languages Folder to support multilingual Report Folder to save report samples Screen DII files which are needed at form configuration 7 SOLGate for DB2 User Manual http www antwiz com 3 3 1 3 1 1 3 1 2 Main Menu File New It creates new SQL Query Editor window SQLGate for DB2 User Manual SQL QUERY EDITOR Select File gt New gt SQL QUERY EDITOR lt Ctri N gt on the main menu or click the SOU button New Connection Select File gt New Connection on the main menu or click the You can connect through not only DB2 but also ODBC ki button Server Information administrator SAMPLE30 e DB2 Client Tab _ DB2 Gient opec Database alias SAMPLE30 dE User administrator Password Date Format wl Use Unicode wl Save Password Date Format Date type format DEFAULT Color A Te
104. ximum value of sequence Increment By Input increment value of sequence e Options Cycle Specify to create sequence value continuously after reaching maximum or minimum value Order Select whether to allocate number orderly when sequence create number Cache Specify whether to preserve some of pre allocated values to memory for faster access This is option for performance and adjustment 45 gt http www antwiz com 3 5 10 New Distinct type 3 5 11 Creates changeable and new Varying Array or Nested Table on the database To create type CREATE TYPE or CREATE ANY TYPE privilege is necessary Select Create gt Distinct type on the main menu New Distinction Type administrator SAMPLE30 Col Jee Schema ADMINISTRATOR y Name Type Distinct w Comment Options La taw Source Data Type INTEGER w La ei La Li La taw bit data View SQL OK Cancel administrator SAMPLE30 Specify a Schema Input a name of creating Distinct type at Name Select a Type Specify each items Check SQL by clicking the View SQL button Click the OK button CrP LY ry E New Structured type Defines user defined Structured type Select Create gt Structured type on the main menu lt 46 gt SQLGate for DB2 User Manual http www antwiz com SO A A UN A SOLGate for DB2 User Manual C3 New Structured Type administrator SAMPL
105. zard Data Type Define additional options for the data file Ki FirstRow 1 Last Row column header Import Wizard Dates Times and Numbers Date Cider Ip n Dsomalsmbei Thousand Separator Four Digit Years Leading Zero Date Separator Currency WW Logical Values True False Time Delimiter First Row First Row to import If there is row name in first row set as 2 Last Row Last Row to import Default is blank 4 Map column of original file onto row of target table lt 54 gt http www antwiz com SOLGate for DB2 User Manual Click the Auto Columns Mapping button to map automatically then make sure that each column is mappe d correctly Data Import Wizard Data Import Wizard f s File Preview File Preview Auto Column Mapping Click the button for mapping and then check whether column Auto Column Mapping Click the button for mapping and then check whether column of the original file and column of the table are same of the original file and column of the table are same Import Wizard 3 101 Neena Kochhar NKOCHHAR 5 3 101 Neena Kochhar NKOCHHAR 5 4 102 Lex De Haan LDEHAAN 5 019 4 102 Lex De Haan LDEHAAN 5 ti r d e r CH Data Exporting fa heetl 7 Sheet AS wm CH Data Exportina A Sheetl A Sheet2 A Sheet3 5 Preview data after checking Column Mapping between target table and original source file Make sure

Product Menual - SQLGate for SQL Server

Contents

Download Pdf Manuals

Related Search

Related Contents