DBMS Interaction (from Fraktal SAS Programming)

DBMS

"Database management systems" (DBMS) found in today's professional or industry environment belong to the family of "relational DBMS" talking "Structured Query Language" (SQL). Over past decades since their appearance on the market only few products (and even fewer companies) have retained their independence or have survived at all. Again, only few of these lucky ones have established "their" markets in selected industries. The spring 2014 market status looks like this:

• IBM DB2 (Financial)
• IBM Informix Dynamic Server
• Ingres VectorWise
• Microsoft SQL Server
• Oracle Database (Pharma)
• Oracle mySQL (Web 2.0)
• SAP Sybase Adaptive Server Enterprise
• Teradata (Mass Retailer)

To interact with the above mentioned family of database systems SAS Institute provides convenient read/write-engines as part of their "Multiple Engine Architecture" (MEA). This means, roughly speaking, that the ordinary SAS user is rarely bothered with specific properties of the particular database server used for data management in his company or working context in general:

From a SAS programmer's view data is data and (almost) nothing but.

Nevertheless, throughout this course, we will consistently refer to the particular DBMS when presenting examples and sample code.

Interaction

In general, the SAS DBMS engines offer three modes of interaction:

• Oracle Libname Engine: Hiding the database elements (tables and views) behind a SAS libref.
• Oracle Hybrid Queries: Using a database connection as valid data source in SQL coding.
• Oracle Passthru SQL: Send database commands to be executed on the database server.

Other Data Sources

Apart from relational DBMS there is a wide choice of products used to manage and store data. These will be dealt with in separated chapter and can be sorted into three categories:

• Spreadsheets (e.g. MS-Excel)
• File based DBMS (e.g. MS-Access)
• Hierarchical DBMS (e.g. IMS/DL1)