1 / 30

Open Query File

Open Query File. Dynamically creating logical views. OPNQRYF Heritage. DclDiskfile at design time:. File name Properties at runtime:. Implementation Differs With Engine. DataGate Windows Simple Query Implements Select/Omit DB2/400 OPNQRYF Implements QRYSLT & KEYFLD parameters

kyrie
Download Presentation

Open Query File

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Open Query File Dynamically creating logical views.

  2. OPNQRYF Heritage • DclDiskfile at design time: • File name Properties at runtime:

  3. Implementation Differs With Engine • DataGate Windows • Simple Query • Implements Select/Omit • DB2/400 • OPNQRYF • Implements QRYSLT & KEYFLD parameters • SQL Server • SQL • Implements SELECT WHERE

  4. Basics • File created in QTEMP • QTEMP in SQL Server introduced: DG-SQL 8.1.433 • Give File any name you wish‡ • *NAME uses DclDiskFile Name keyword value ‡ • *UNIQUE creates a unique name ‡ Avoids risk of duplicate file names • Sequential Access is Intended • Random Access Allowed (some restrictions apply)

  5. QRYSELECT for DataGate Windows • Select/Omit Rule • Relational Operators: • Use either ' or " for literal token • Field to Field comparison allowed • No built in functions

  6. DataGate Windows Examples

  7. QRYSELECT for DB2/400 • Whatever is valid in QRYSLT • Relational Operators: • Use either ' or " for literal token • Field to Field comparison allowed • Lots of built in functions

  8. DB2/400 Examples

  9. QRYSELECT for SQL Server • Whatever is valid following Select From WHERE • Relational Operators: • Use only ' for literal token • Field to Field comparison allowed • Lots of built in functions • Wildcards allowed

  10. SQL Server Examples

  11. QRYKEYFLDS • Specifies presentation sequence of records • ORG( *indexed and *Arrival ) • Target File can be Logical or Physical • Syntax • Field name • Ascending or Descending • Absolute value (numeric fields only)

  12. When Things Go Wrong – DG Windows

  13. Copy and Paste QRYSelect

  14. Need A Little Help? Use Default Select Tab Notice the Drop Downs

  15. What's wrong with this? When Things Go Wrong – DB2/400

  16. WRKACTJOB Check the Job Log for DataGate Job

  17. What's wrong with this? When Things Go Wrong – SQL Server

  18. Use SQL Server Management Studio

  19. Use SQL Server Management Studio

  20. Random or Sequential Processing? • Open Query intended to be sequential • Random Access commands: • CHAIN; SETLL; SETGT; nnnRANGE • Random is OK • If QRYKeyFlds matches compiled file's key parts • If you say so (see Random keyword) • dclDiskFile's Random Keyword • Tells Compiler of your intention • *NO raises compiler error if you use random commands

  21. Single Source? • Syntax and punctuation differences can be normalized in code • e.g., use <>instead of*NE • How do you do know to which engine application is connected at runtime?

  22. Database Class Members

  23. Database Properties

  24. Server Host Name or IP address of DB server Port IP Port for DB service Label Identifies DB Instance PlatformAttribute Identifies server linkage PoolingTimout 0 = No pooling 1-255 = Pool Idle TimeOut User User ID or *DOMAIN Password User password if not *DOMAIN Distinguishing DB Object Properties

  25. Use Server Property? • IP Address • 10.1.3.25 • Host Name • "S1055523" • "NetServ3500\MySQLServerName" • Not cast in bronze • *LOCAL • My Computer for DG Windows instances • My Computer for Default SQL Server instance

  26. Use Port Property? • 0 or 5042 • *LOCAL • 5042 • Required for Remote DG SQL Server • Default for DG iSeries • nnnn • Multiple DG iSeries versions

  27. Use PlatformAttribute Property? • *DATALINK • Native DataGate protocol • DG iSeries • DG Windows • *SQLOLEDB • SQL Server 2000 & 2005

  28. Use Label Property? • Identifies DG Windows DG location • Not meaningful for DG iSeries or DG SQL Server • PlatformAttribute Property distinguishes iSeries from SQL Server • So, use meaningful Label to identify iSeries • e.g., "AS400", "iSERIES", "SYSTEM i"

  29. Mix in ADO for Embedded SQL

  30. Open Query File 30

More Related