Using Oracle EXPAND_SQL_TEXT

Database tables are not static entities; besides the usual insert/update/delete events, occasional DDL can be executed to add columns, drop columns or add needed constraints or indexes. The first two items can create problems with stored procedures, packages, functions and possibly triggers by changing the number of columns that need to be processed when explicit variables are used. If the programmer used a record variable (as shown below) then no issues are likely to be seen:

SPLEEBO @ gwankus >
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Code to display all employee information
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- This should succeed
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > declare
  2       cursor get_emp_info is
  3       select * From emp;
  4  begin
  5       for emp_rec in get_emp_info loop
  6       dbms_output.put_line(emp_rec.empno||' '||emp_rec.ename||' '||emp_rec.job||' '||emp_rec.mgr||' '||emp_rec.hiredate||' '||emp_rec.sal||' '||emp_rec.comm||' '||emp_rec.deptno);
  7       end loop;
  8  end;
  9  /
7369 SMITH CLERK 7902 17-DEC-80 800  20                                       
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30                                 
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30                                 
7566 JONES MANAGER 7839 02-APR-81 2975  20                                     
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30                               
7698 BLAKE MANAGER 7839 01-MAY-81 2850  30                                     
7782 CLARK MANAGER 7839 09-JUN-81 2450  10                                     
7788 SPLEEBO ANALYST 7566 09-DEC-82 3000  20                                     
7839 KING PRESIDENT  17-NOV-81 5000  10                                       
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30                                 
...
6100 MILLER CLERK 7782 23-SEP-97 1300  10                                     
SPLEEBO @ gwankus >
If explicit variables are used, then it's likely the code will fail because of the change. It can be disconcerting to see the following in an error message:

PLS-00394: wrong number of values in the INTO list of a FETCH statement

Of course investigation will reveal an 'alter table ...' statement was executed prior to the failure or a describe on the affected table will report a different number of columns than were present when the code was originally written. Once this fact is known, fixing the problem is up to the developer who wrote it. They will have choices exist on how to go about making such changes.

Looking at an example using explicitly coded variables and an added column, let's explore using a procedure from the DBMS_UTILITY package, EXPAND_SQL_TEXT, to generate a complete column list from the modified table to use as a reference for code changes. First, the original code and the error it generates:

SPLEEBO @ gwankus >
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Code to display all employee information
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Fails because not enough variables are declared
SPLEEBO @ gwankus > -- and populated
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > declare
  2       v_empno emp.empno%type;
  3       v_ename emp.ename%type;
  4       v_job   emp.job%type;
  5       v_mgr   emp.mgr%type;
  6       v_hiredate      emp.hiredate%type;
  7       v_sal   emp.sal%type;
  8       v_comm  emp.comm%type;
  9       v_deptno      emp.deptno%type;
 10 
 11       cursor get_emp_info is
 12       select * From emp;
 13  begin
 14       open get_emp_info;
 15       loop
 16       fetch get_emp_info into v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno;
 17       exit when get_emp_info%notfound;
 18       dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||'  '||v_sal||' '||v_comm||' '||v_deptno);
 19       end loop;
 20  end;
 21  /
fetch get_emp_info into v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno;
*
ERROR at line 16:
ORA-06550: line 16, column 3:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 16, column 3:
PL/SQL: SQL Statement ignored

SPLEEBO @ gwankus >
SPLEEBO @ gwankus >

Create a copy of the original code (to preserve it should it be needed) and use DBMS_UTILITY.EXPAND_SQL_TEXT to generate, as a comment, the expanded results of a 'select * from ..." query against the modified table. The procedure requires a CLOB variable be declared to hold the results of the procedure call and also requires the specific 'select *' query to operate on. The code shown below generates this output as a comment and can be reused by editing the table name in the supplied query:

SPLEEBO @ gwankus > --
SPLEEBO @ gwankus >-- Expand the 'select *' query to see all
SPLEEBO @ gwankus >-- of the returned columns
SPLEEBO @ gwankus >--
SPLEEBO @ gwankus >-- Add the output to the failing script
SPLEEBO @ gwankus >-- to facilitate corrective edits
SPLEEBO @ gwankus >--
SPLEEBO @ gwankus >spool new_query.sql
SPLEEBO @ gwankus >declare
  2    l_clob clob;
  3  begin
  4    dbms_utility.expand_sql_text (
  5      input_sql_text  => 'select * from emp',
  6      output_sql_text => l_clob
  7    );
  8
  9    dbms_output.put_line('/*');
 10    dbms_output.put_line(lower(l_clob));
 11    dbms_output.put_line('*/');
 12  end;
 13  /

/*                                                                             
select "a1"."empno" "empno","a1"."ename" "ename","a1"."job" "job","a1"."mgr"   
"mgr","a1"."hiredate" "hiredate","a1"."sal" "sal","a1"."comm"                 
"comm","a1"."deptno" "deptno","a1"."term_dt" "term_dt" from "scott"."emp" "a1" 
*/                                                                             

PL/SQL procedure successfully completed.

SPLEEBO @ gwankus > spool off

Create a working copy and append the output of the above query to it. Then, open the modified file in the editor of choice to make the necessary changes:

SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Copy the original script to preserve code
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > !cp emp_info_pl_orig.sql emp_info_pl.sql

SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Add the output generated above as a comment
SPLEEBO @ gwankus > -- for reference purposes
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > !cat new_query.sql >> emp_info_pl.sql

SPLEEBO @ gwankus >
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Edit the script copy to fix the issue by
SPLEEBO @ gwankus > -- adding the necessary variable declaration
SPLEEBO @ gwankus > -- and editing the code to populate it
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > !vi emp_info_pl.sql

declare
        v_empno emp.empno%type;
        v_ename emp.ename%type;
        v_job   emp.job%type;
        v_mgr   emp.mgr%type;
        v_hiredate      emp.hiredate%type;
        v_sal   emp.sal%type;
        v_comm  emp.comm%type;
        v_deptno        emp.deptno%type;
        v_term_dt        emp.term_dt%type;

        cursor get_emp_info is
        select * From emp;
begin
        open get_emp_info;
        loop
                fetch get_emp_info into v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno, v_term_dt;
                exit when get_emp_info%notfound;
                dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||' '||v_sal||'
                            '||v_comm||' '||v_deptno||' '||v_term_dt);
        end loop;
end;
/

/*                                                                             
select "a1"."empno" "empno","a1"."ename" "ename","a1"."job" "job","a1"."mgr"   
"mgr","a1"."hiredate" "hiredate","a1"."sal" "sal","a1"."comm"                 
"comm","a1"."deptno" "deptno","a1"."term_dt" "term_dt" from "scott"."emp" "a1" 
*/     
                                                                       
Test the changes to ensure everything works as expected:

SPLEEBO @ gwankus >
SPLEEBO @ gwankus > set head on feedback on pagesize 60
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Run modified code
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- The anonymous block now completes
SPLEEBO @ gwankus > -- without error
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > @emp_info_pl
SPLEEBO @ gwankus > declare
  2       v_empno emp.empno%type;
  3       v_ename emp.ename%type;
  4       v_job   emp.job%type;
  5       v_mgr   emp.mgr%type;
  6       v_hiredate      emp.hiredate%type;
  7       v_sal   emp.sal%type;
  8       v_comm  emp.comm%type;
  9       v_deptno      emp.deptno%type;
 10       v_term_dt       emp.term_dt%type;
 11 
 12       cursor get_emp_info is
 13       select * From emp;
 14  begin
 15       open get_emp_info;
 16       loop
 17       fetch get_emp_info into v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno, v_term_dt;
 18       exit when get_emp_info%notfound;
 19       dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||' '||v_sal||' '||v_comm||' '||v_deptno||' '||v_term_dt);
 20       end loop;
 21  end;
 22  /
7369 SMITH CLERK 7902 17-DEC-80 800  20 31-DEC-99                             
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 31-DEC-99                       
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 31-DEC-99                       
7566 JONES MANAGER 7839 02-APR-81 2975  20 31-DEC-99                           
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 31-DEC-99                     
7698 BLAKE MANAGER 7839 01-MAY-81 2850  30 31-DEC-99                           
7782 CLARK MANAGER 7839 09-JUN-81 2450  10 31-DEC-99                           
7788 SPLEEBO ANALYST 7566 09-DEC-82 3000  20 31-DEC-99                           
7839 KING PRESIDENT  17-NOV-81 5000  10 31-DEC-99                             
...
6100 MILLER CLERK 7782 23-SEP-97 1300  10 31-DEC-99                           

PL/SQL procedure successfully completed.

SPLEEBO @ gwankus > --

This same technique can be used on tables with a relatively large number of columns:

SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Let's take another example
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Create a table with 21 columns
SPLEEBO @ gwankus > -- and populate it
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > @lotsa_cols
SPLEEBO @ gwankus > create table lotsacols(
  2  a1      number,
  3  a2      number,
  4  a3      number,
  5  a4      number,
  6  a5      number,
  7  a6      number,
  8  a7      number,
  9  a8      number,
 10  a9      number,
 11  a10     number,
 12  a11     number,
 13  a12     number,
 14  a13     number,
 15  a14     number,
 16  a15     number,
 17  a16     number,
 18  a17     number,
 19  a18     number,
 20  a19     number,
 21  a20     number,
 22  a21     number);

Table created.

SPLEEBO @ gwankus >
SPLEEBO @ gwankus > begin
  2       for z in 1..1000 loop
  3       insert into lotsacols(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21)
  4       values(mod(z,3)+1,mod(z,13)+1,mod(z,21)+1,mod(z,34)+1,mod(z,47)+1,mod(z,53)+1,
mod(z,67)+1,mod(z,79)+1,mod(z,81)+1,mod(z,97)+1,mod(z,3)+1,mod(z,7)+1,mod(z,6)+1,mod(z,2)+1,
mod(z,9)+1,mod(z,8)+1,mod(z,101)+1,mod(z,407)+1,mod(z,313)+1,mod(z,271)+1,mod(z,133)+1);
  5       end loop;
  6 
  7       commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SPLEEBO @ gwankus >
SPLEEBO @ gwankus >
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Rather than do a DESC on the table
SPLEEBO @ gwankus > -- use expand_sql_text to generate the
SPLEEBO @ gwankus > -- column list and spool it to a file for
SPLEEBO @ gwankus > -- later use
SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Edit that file to create a working block
SPLEEBO @ gwankus > -- of PL/SQL to generate results from the
SPLEEBO @ gwankus > -- table data
SPLEEBO @ gwankus > --

/*                                                                             
select "a1"."a1" "a1","a1"."a2" "a2","a1"."a3" "a3","a1"."a4" "a4","a1"."a5"   
"a5","a1"."a6" "a6","a1"."a7" "a7","a1"."a8" "a8","a1"."a9" "a9","a1"."a10"   
"a10","a1"."a11" "a11","a1"."a12" "a12","a1"."a13" "a13","a1"."a14"           
"a14","a1"."a15" "a15","a1"."a16" "a16","a1"."a17" "a17","a1"."a18"           
"a18","a1"."a19" "a19","a1"."a20" "a20","a1"."a21" "a21" from                 
"scott"."lotsacols" "a1"                                                       
*/                                                                             

declare
    cursor get_lotsa is
    select * From lotsacols;
begin
    dbms_output.put_line('Your lucky LOTTO numbers are: ');
    for lotsa in get_lotsa loop
        dbms_output.put_line(lotsa.a1||' '||lotsa.a6||' '||lotsa.a7||' '||lotsa.a13||' '||lotsa.a17||' '||lotsa.a20);
    end loop;
end;

/

SPLEEBO @ gwankus > --
SPLEEBO @ gwankus > -- Execute the code
SPLEEBO @ gwankus > --

Your lucky LOTTO numbers are:                                                 
2 50 8 5 7 209                                                                 
3 51 9 6 8 210                                                                 
1 52 10 1 9 211                                                               
...
2 53 11 2 10 212                                                               
1 32 51 1 14 179                                                               
2 33 52 2 15 180                                                               
3 34 53 3 16 181                                                               

PL/SQL procedure successfully completed.

Using EXPAND_SQL_TEXT can be easier than generating a table listing using DESC and spooling the results as it creates a smaller file that can easily be incorporated into a change procedure. Since the expanded SQL text is generated as a comment, it can remain after the edits are completed in the event further code changes are required or desired.

The choice is the developer's to make, but it certainly seems easier to let Oracle generate usable output in a somewhat automated fashion to facilitate code edits. In the end it's whatever the developer is comfortable with that matters most. But, it might be worth investigating using EXPAND_SQL_TEXT to put that reference information in the script being edited and possibly avoid getting lost between two screens of code. That could save editing time.

Continue reading

Polling and Survey Data Transparency

Polls and surveys are used to obtain or confirm opinions. Polling and survey databases provide order for questions, replies, and analysis. Polls and surveys are conducted by news, educational, and commercial organizations such as The Wall Street Journal, CNN, Fox News, Marist College, Quinnipiac University, Pew Research Center, and Hart Research.

How Does a Poll Differ from a Survey?

Polls and surveys are not exactly the same. Polls ask a single, simple question. There are usually multiple predefined answers. Polls are a subset of surveys. Surveys generally seek information through a wide range of questions and statements, including binary, multiple choice and freeform text responses.

Polls are generally conducted via telephone and/or face-to-face interviews. Surveys are often conducted via questionnaires through mail and on-line applications.

Polls and surveys might be completed anonymously. Demographics appropriate for the poll or survey are the basis for selecting participants from data sources.

Topics for Polls and Surveys

Organizations seek information on a variety of topics. They conduct their own research or engage a firm to conduct polling and surveys. Topics with an archive of results from which to draw design and results vary by industry include:

◉ Brand
◉ Climate change
◉ Cost of living
◉ Crime
◉ Economy
◉ Education
◉ Employment
◉ Energy
◉ Global activity
◉ Healthcare
◉ Immigration
◉ Morality
◉ Product
◉ Quality of life
◉ Service
◉ Terrorism
◉ Wages
◉ War

It's a best practice to have reusable libraries of code, data models, and content. For a survey database, the reusable content would contain questions and statements for polls and surveys. Configurable attributes might include topic, priority, weight, and more.

Surveys and polls have user experiences for the respondents, authors, analysts, and developers. Each of these audiences expects to interact with a survey for different reasons. Developers see surveys as code and data. Authors create the content for a survey, including questions and statements that might appear in multiple surveys. The metadata for these questions is used by analysts to examine topics, adjust weights, and change priorities within a survey.

Polls and surveys are used to extrapolate positions and trends. A few hundred participants can represent the position of millions of people with a margin of sampling errors of a few percentage points. Poll results rarely differ with tens of thousands of respondents instead of a few thousand. Accuracy depends on participant sources, anonymity, language, technology, screening, and confirming data.

Data Modeling

Data modeling to implement a poll or survey system results in conceptual, logical, and physical representations. The conceptual data model is formed from discussions with people that will interact with the system. The people include system administrators, survey authors, analysts, and respondents. A conceptual data model from such discussions can result in a conceptual model with requirements for Survey Administration, Account Administration, Questions, and Analysis. Further examination of these data concepts follows.

Survey Administration

Individual statements during requirements gathering sessions provide demographics for a given survey. Age is always considered. Address, vocation, family status, and specific activities related to the poll topic are identified. Rinse and repeat for the next poll or survey topic. Over time, many demographic elements are defined. In later data modeling, the demographics are several entities and attributes. The standard operating procedure for addresses, for instance, includes separating the number from direction, roadway name, and roadway type. So, "764 E. Walnut St." is stored in four fields in an Address table. Should "E." best stored as stated, or transformed to "East?"

Attributes might be non-textual, such as image or video or audio files. These files each have a range of associated metadata such as date/time created, size in bytes, resolution, location, recognized instruments, and known colors. The decision on the modeling and implementation of physical models in a technology or product or product version are too early for conceptual modeling.

Surveys carry administrative information. Eventually, storage of the survey name, sponsor, question collection, priority, and dependencies on other activities will be expected.

Dates of survey creation, initiation, first used, last used, and ranges of dates are valuable to be available to analysts after polls and surveys are completed. How often do you see poll results for a poll taken recently, over a few days?

Methodology

Each of the organizations mentioned earlier follow a specific methodology for each poll or survey conducted. The methodology becomes a "pick list" for developers and analysts, as seen in this sample:

◉ Each respondent was asked to rate a total of 40 randomly selected brands

◉ Each brand received approximately 1,000 ratings

◉ Data was weighted to be representative of the entire U.S. population of consumers

     ◉ ages 15 and over

     ◉ based on age by sex

          ◉ education

          ◉ race/ethnicity

          ◉ region

          ◉ income

          ◉ data from respondents ages 18 and over weighted for their online presence

Account Administration

Surveys are generally conducted on behalf of a sponsor. Details of the organization, industry, size, logos, images, brands, and colors must be maintained. The interactions with the sponsor form an account management process. The people and their roles within the process are defined in this data concept. Note that conceptual data management allows the downstream technology to be defined later. Modern external systems always have a way to communicate and provide data in packets via API calls. The need for extracting and transforming millions of rows hourly or daily are disappearing.

Questions

Polls and surveys might be conducted in multiple human languages. Questions that are asked in multiple surveys are seen as collections. Collections of questions are useful for meeting time and governance requirements. Formats determine the handling of binary, multiple choice, freeform text, and non-textual replies.

Analysis

Aggregating data across surveys is invalid, generally, due to varying methodologies. A requirement on weighting of replies within a survey is seen in the analysis stage. Reports and visualizations are popular outputs of data elements. The ability to run a SQL statement against a few tables with proper levels of joins is a developer task. The physical data likely does not exist in a single technology or network.

Logical and Physical Data Models

Logical data modeling for a domain as broad as polls and surveys has been refined for decades by the organizations named herein. New delivery and analysis technologies cause reviews. Customer needs for including modern elements of audio, video, images, and social media streams are implemented in physical models. Underlying programming concepts bring data values from disparate systems to the analysis layer.

Data Transparency

Who has your data? Is it your data when aggregated without identifying data? This is at the heart of the General Data Protection Regulation (GDPR), a regulation to strengthen and unify data protection for individuals within the European Union. Will it become a standard elsewhere?

Identification, export, and deletion of personal data is a task to be implemented in any survey containing personal information. Social Security numbers, financial accounts, addresses, birthdates, and interaction dates must be stored in a manner known to the participants whose data is being handled. Distribution of raw data should be noted in the methodology and account management processes.

Continue reading

How Many Databases Can You Name?

How many different database applications exist? Many people can name the most common ones such as MySQL, Microsoft SQL Server, and Oracle. Most good DBAs and programmers could likely rattle off a dozen different databases is you asked them to.

This begs the question - how many can you name?

Before looking at the list below or reading any further, I challenge you to consider a number. More so, I challenge you to then write your list to see if you can meet the number you selected! To be clear - SQL Server 2016 and SQL Server 2018 count as the same database. Different release versions of the same database count as one!

I believe that I could rattle of a dozen easily. I believe that I could get to two dozen without too much of a struggling. I named three above, and if you simply think of major software companies and variants of those you can come up with several more.

I won't say what number I ended up listing without researching. With a little research, I have generated the list below that has well over 100 different databases included.

How many did you believe you could name? How many did you do? Post in a comment on this article your numbers. Did you see databases missing from our list? Feel free to include them in your comments as well!

The List of Databases

Here is a list of over 100 different databases. Different versions of the same database were not included.

◉ 4D

◉ Action PSQL

◉ ADABAS

◉ Adaptive Server Enterprise

◉ Altibase

◉ Amazon Aurora

◉ Amazon DocumentDB

◉ Amazon DynamoDB

◉ Amazon Redshift

◉ Amazon SimpleDB

◉ Apache Accumulo

◉ Apache Cassandra

◉ Apache Derby

◉ Apache Hbase

◉ ArangoDB

◉ Axisbase

◉ Bigtable

◉ Caché

◉ Cemstone/S

◉ Clusterpoint database

◉ Clustrix

◉ CockroachDB

◉ ConceptBase

◉ Cosmos DB

◉ CouchBase

◉ CouchDB

◉ CUBRID Database

◉ D3 Pick database

◉ Database .NET

◉ Database Labs

◉ Datacom

◉ Db4o

◉ dBase

◉ Dbeaver

◉ dbHarbor: SQLite

◉ DeepDB

◉ Empress Embedded Database

◉ EXASolution

◉ Extensible Storage Engine (ESE/NT)

◉ FileMaker

◉ Firebird

◉ FlockDB

◉ Freebase

◉ Frontbase

◉ GemStone/S

◉ GigaSpaces

◉ GPUdb

◉ Greenplum Database

◉ H2 Database Engine

◉ HP NonStop SQL

◉ HSQLDB

◉ IBM Cloud NoSQL Database Service

◉ IBM DB2

◉ InfinityDB

◉ Informix

◉ Ingres

◉ Interbase

◉ JADE

◉ jBASE Pick database

◉ Kexi

◉ LibreOffice Base

◉ Linter SQL RDBMS

◉ LucidDB

◉ MariaDB

◉ MariaDB

◉ MarkLogic

◉ Matisse

◉ MaxDB

◉ MemSQL

◉ Micro Focus XDB Enterprise Server

◉ Microsoft Access

◉ Microsoft Azure SQL Database

◉ Microsoft SQL Server

◉ Microsoft Visual FoxPro

◉ Mimer SQL

◉ Mnesia

◉ MonetDB

◉ MongoDB

◉ mSQL

◉ mvBase

◉ mvEnterprise

◉ MySQL

◉ NexusDB

◉ NuoDB

◉ ObjectDatabase++

◉ ObjectDB

◉ ObjectStore

◉ ODABA

◉ Omnis Studio

◉ Open Sqlite

◉ OpenAccess

◉ OpenBase SQL

◉ OpenEdge Advanced Business Language (Progress 4GL)

◉ OpenLink Virtuoso

◉ OpenQM

◉ Oracle Database

◉ Oracle TimesTen

◉ OrientDB

◉ Paradox

◉ Perst

◉ Pervasive PSQL

◉ Picolisp

◉ Polyhedra RDBMS

◉ PortaBase

◉ PostgreSQL

◉ Postres-XC

◉ Profium Sense

◉ R:Base

◉ Raima Database Manager

◉ RDM Server

◉ RethinkDB

◉ RSQL

◉ SAP HANA

◉ SAP MaxDB

◉ ScimoreDB

◉ Sequel Pro

◉ solidDB

◉ SQL Anywhere

◉ SQLBase

◉ SQLGate

◉ SQLite

◉ SQream DB

◉ Sqrrl Enterprise

◉ Starcounter

◉ Superbase

◉ Sybase Advantage Database Server

◉ Symphytum

◉ Tarantool

◉ Tecknolab Dbcloudbin

◉ Teradata

◉ TIBCO ActiveSpaces

◉ Tibero

◉ TiDB

◉ Titan Database

◉ Trafodion

◉ UniData

◉ Unisys RDMS 2200

◉ UnQLite

◉ Valentina Server

◉ Versant Object Database

◉ Vertica

◉ VistaDB

◉ VMDS

◉ VoltDB

◉ WakandaDB

◉ YugaByte DB

◉ Zope Object Database

Continue reading

SQL Server Management Features vs Oracle Database Management Features

Notification Services

SQL Server Notification Services, referred to as SQLNS, was meant to be an all-purpose message or messaging delivery system. Its architecture is based on a subscriber, subscriptions, one or more events, and a notification. If you have events whose occurrence you want messaged to a user, that user could receive a notification via PDA, SMS text, and other forms of delivery.

If you are familiar with Oracle’s BI Publisher application, what SQLNS is similar to is the scheduling and bursting of reports. The concept is that you can send versions of whatever information to whomever in various formats. Overall, SQLNS sounds like a fairly decent feature. The problem is that it was kind of a non-starter and it wasn’t widely adopted (not that this has ever happened to any of Oracle’s products…). So, going forward in releases of SQL Server 2008, SQLNS is not to be found. However, the functionality found in SQL Server 2005 can be used against or applied to 2008.

There are also some additional licensing requirements associated with SQLNS, just like there are with BI Publisher.

SQL Server Agent

The usefulness and utility of the SQL Server Agent cannot be overstated. So many things within SQL Server are and can be driven by the Agent. So much so that the Agent can be overused, and most of the issues regarding this concern security. The Agent is fairly secure within its own domain (i.e., server or instance it runs on or in), but is not so secure outside of that area. The running of the Agent, with respect to it running as a service, is often tied into the overall MSSQL service in the first place. As an analogy, Oracle supports the automatic starting of an instance via init.d scripts, so whatever is needed or associated with the Oracle instance can also be started along with the instance (listener, OEM, Database Control, etc.). The same holds true with the SQL Server instance and its agent service.

Outside of what it takes to run a database, pretty much all other maintenance, alert, logging, and notification tasks fall onto the shoulders of the Agent. Upon examining the sub items under the Agent in the Object Explorer tree, that notion should be fairly clear.

Jobs

All jobs, regardless of what they are or which category they fall under, can be run by the Agent. Backups, index maintenance, and reports run via stored procedures (or pasted in T-SQL statements/queries), to name a few, are examples of what the Agent executes for you.

When does the Agent perform a job? Much like Oracle Database, it is done in one of two ways: on a schedule or on an ad hoc basis. The creation of a job is very much like how Oracle jobs or schedules are created (conceptually, not so much syntax-wise). Right-click Jobs and create a new one. The dialog window that next appears is shown below (with Name filled in by me; otherwise it is blank).

All you have to do is work your way down the options in the “Select a page” pane. Once everything is filled in or entered, click the Script button in the top menu bar to generate a script. You’ll see many commands/statements, but they’re fairly readable and you will be able to see similarities between MSSQL and Oracle.

In Oracle, how would you view the status of job? Examining V$SCHEDULER_RUNNING_JOBS is one way, and navigating through Database Control is yet another. In MSSQL, you can fire off the Job Activity Monitor (whose location has changed in SQL Server 2008, by the way). The GUI display via Database Control is close to the tabular display MSSQL presents. Just like in Oracle, you can view the history of a job via Management Studio.

Alerts

One feature common to both systems is what you can do with how and when alerts are managed. When the 10g version of Enterprise Manager/Database Control came out, the event notification feature was a nice touch. Unfortunately, clearing alerts could be somewhat problematic and the interface was clunky. Not so with the Alerts feature within MSSQL. You get an alert, deal with it, it is recorded in a log file, and move on. You are not stuck with a visual display of a stale alert for some indeterminate amount of time.

For the interface, if your SQL Server instance is running via the service, and you have SQL Server Management Studio installed, you will have access to alert management. You are not going to see events like what is shown below in Database Control.

Here we see that the console is at least running as a web service.

Click Login and now we have an obscure Java error stack.

Anyway, SSMS has a drop down list of values for all kinds of alert events, some of which are shown below.

Further, alerts can also be set against a specific database, with <all databases> being the default. Moreover, if you prefer, you can flag an alert based on the error number or when the message text matches your criteria.

Operators

The concept of an Operator in SQL Server is slightly different from a schema in Oracle database. An operator can be a utility type of account that has nothing to do with data or other objects. It’s just a “person” who is set up to send messages for you. Once you have an alert (or other object, such as a job) configured, and there is a need to send a message based on an event or alert, the operator is basically the sending agent for you. A very common step in configuring a new MSSQL instance is setting up Database Mail. Hand-in-hand with database mail is the account that is going to be the sender in the message header.

Proxies

Expand the Proxies item to see the following list.

What do these items have in common? It may not be readily clear, but what they have in common is that these are all interfaces into features or areas outside of the MSSQL instance and its databases. A very common requirement in Oracle is a need to access something on the file system (or at least be able to run an OS command). You need some type of security context for this. Think about a directory object in Oracle. You create it, and then grant read/write to a user. Underneath all of this is the requirement that the “oracle” account (typically) has file permissions itself on that location. In that context, the oracle OS account is your proxy into the file system. Same thing here in MSSQL.

Do you have to create a proxy for each and every context? No. Right-click a proxy item and continue with creating a new proxy. In the wizard-like dialog window, you can actually create a proxy, which has access to more than one subsystem (select the Subsystem check box to select all items).

Just like many other features in Oracle, you probably wouldn’t want to be overly generous in adding unnecessary privileges (e.g., don’t grant DBA to all users kind of idea). Once a proxy has been defined, you then add a principal (what MSSQL roughly calls a login account). Users in the sysadmin group will have access to a proxy by default.

Error Logs

Error Logs is the very last item in the Object Explorer, but certainly not the least in terms of usefulness. At first blush, this looks like access to only the MSSQL Agent error logs, but upon selecting a log (current or otherwise), the interface in SSMS allows you to select other logs as well (the SQL Server log list also has a corresponding Current and Archive #X list).

For example, what you see under Windows NT are the same logs you can get to via Computer Management.

There’s nothing new about this, but it is a convenient shortcut and is something that Oracle definitely does not have (at least by default, not that you can’t hack into OS logs otherwise). Oh, and that drill of rolling alert logs in Oracle? This chore is much easier to deal with in MSSQL.

Continue reading

An Introduction to Control Center Personalities in DB2 Universal Database

In this article, we will introduce you to a new feature of the Control Center that is planned for the DB2 UDB "Stinger" release called: Control Center Personalities. Personalities allow you to control the surfaced objects and the actions that you can perform on them.

Why Control Center "Personalities"?

The Control Center provides a complete range of functionality and features for all users, regardless of their skill set. For experienced DBAs, or "power" users, all of this functionality presented at all times can complicate navigation of the Control Center and make task execution more inefficient.

For example, an ACCPAC business partner is likely to use the Control Center merely to run the Configuration Advisor and create a schema object. Before "Stinger," this novice DB2 user might have been confused when drilling down the Control Center's object tree to find the database, wondering what an instance is, and so on.

The Control Center before "Stinger" looked like the following figure:

Notice how long the object tree under the Databases folder is when fully expanded. It doesn't even fit on the screen.

In DB2 UDB "Stinger," personalities can be used to simplify navigation and reduce the potential complexity for users who do not need an all-encompassing management tool for their DB2 database environment. With personalities, you can use the Control Center in either Basic mode or Advanced mode (in this mode, the Control Center functions like it did before "Stinger"), or customize the Control Center's surfaced objects and the actions that can be performed on them.

Starting the Control Center in DB2 UDB "Stinger"

When the Control Center starts, the Control Center View window (shown below) opens. This window allows you to select a personality for the Control Center's current run time.

If you want the selected personality to persist, so that it starts the same way each time, deselect the Show this window at startup time check box.

If you've configured the Control Center to always start with the Basic personality, but you later want to perform a more advanced function, or perhaps want to customize the Control Center, you can change the personality without restarting the Control Center by clicking on Tools-->Tools Settings, and selecting Customize Control Center (shown below), which opens the Control Center View window shown in the previous figure.

Control Center Personalities

In the previous section, we showed you how to start the Control Center with the personality that you want to use. In this section, we will describe each of the personalities in more detail. (We will not discuss the Advanced personality in any great detail, because that personality is the expected behavior of the Control Center before "Stinger".)

The Basic Personality for Basic Users

In the Control Center object tree before "Stinger," all objects are children of the All Systems parent folder. In "Stinger," a new folder named All Databases is displayed (this folder is available for all personalities). The All Databases folder contains all the cataloged databases on your system. Using this folder, you can see all databases at once (even if they reside in different instances), instead of having to drill down through each individual system and its respective instances. This can be very helpful, because DBAs typically only care about the databases that they are working with, and not their respective instances or systems (which are left more to system administrators or more advanced DBAs).

With the Basic personality, the object tree and its respective pop-up action menus are simplified and contain a subset of objects (and associated actions) that would be of interest to most novice DBAs. For example, the Instances and All Systems folders are hidden in this personality, along with many of the objects that would normally appear.

The surfaced objects and actions that are associated with the Basic personality are the result of multiple user-centered design (UCD) test cases, and were validated with DBAs possessing Oracle, DB2, or SQL Server experience.

If you were to select the Basic personality from the Control Center View window, the Control Center would look something like the following figure:

You can see how simple the object tree is in this figure. Not only are the Systems and Instances folders removed, but the structure below each database is drastically simplified as well. The Basic personality will not show any system tables when you select the Tables folder.

In addition to a reduction in the various objects that the DBA is presented with, the actions that can be performed on them is also limited in this personality. For example, in the Basic personality, you do not have the Create From Import option when right-clicking on the Tables folder that you have with the Advanced personality.

The following figure compares the pop-up windows that open as a result of right-clicking on a database when the Control Center is running with the Advanced and Basic personalities:

The Customized Personality for Tailored Users

In "Stinger," the Control Center's Basic personality is a static (predefined) view that is delivered as part of the Control Center. Even though this personality suits the needs of many users, there will always be environments in which a user's particular needs cannot be fulfilled by this personality. In fact, even experienced DBAs might want a way to remove objects that are not used (for example, the Nicknames folder is only needed if you are leveraging the DB2 federated capabilities). In such cases, you need to be able to modify either the Basic or the Advanced pre-defined views and create your own customized views. The Custom option contains a checklist of the objects and pop-up actions that you can select from the Advanced personality. You can prune objects or actions that you do not need and, of course, you can always add them back in later.

To customize which objects will appear, and the actions that you can perform on them, select the Custom radio button, and click Modify. In the future, if you want to start the Control Center with your customized personality, you simply select the Custom radio button and click OK, or simply deselect the Show this window at startup time check box.

Using a Custom personality, you can configure two levels of personalization. You can customize actions on a selected object (perhaps hiding some pop-up actions and displaying others), and folders (again, hiding some, and showing others).

The following figure shows the interface that you can use to customize the Control Center:

When you select an object, the actions that you can perform on that object are listed in the right-hand side of the Customize Control Center View window, as shown in the next figure.

Note that there are usually two levels to a database object. For example, selecting the Tables folder gives you the opportunity to configure what actions would be surfaced in the Control Center for that folder; however, to configure the operations that a DBA could perform on individual tables, you would have to drill down through the Tables folder and select Table Objects (as shown below):

For example, in the previous figure, we configured the Control Center so that whenever it starts with a Custom personality, and you select a table object, you will only be able to open, query or copy that table through the Control Center:

The following figure compares the actions that you can perform on a table in the ACCPAC database, under each of the three personalities (remember, the Basic personality does not show system tables):(i.e., change "both Basic and Custom" to "each of the three")

Wrap-up

As you can see, Control Center personalities can be quite useful: They help novice users to interact with only the database objects that they know and understand, and they allow experienced users to prune out folders and actions that "get in the way." Independent Software Vendors (ISVs) that ship DB2 with their products can also take advantage of the Control Center's personalities by customizing the objects and operations that users of their software will see.

In the last couple of months, we have been introducing you to many of the new features that are coming in DB2 UDB "Stinger." However, there are over 200 new features in DB2 "Stinger." For example, did you notice the new Control Center dashboard in the screen caps throughout this article? This window displays information about the selected object and the most common activities that can be performed on it.

Continue reading

Multitenant : Overview of Container Databases (CDB) and Pluggable Databases (PDB)

Overview

The multitenant option represents one of the biggest architectural changes in the history of the Oracle database. The option introduced the concepts of the Container Database (CDB) and Pluggable Database (PDB).

◉ Container Database (CDB): On the surface this seems very similar to a conventional Oracle database, as it contains most of the working parts you will be already familiar with (controlfiles, datafiles, undo, tempfiles, redo logs etc.). It also houses the data dictionary for those objects that are owned by the root container and those that are visible to all PDBs.

◉ Pluggable Database (PDB): Since the CDB contains most of the working parts for the database, the PDB only needs to contain information specific to itself. It does not need to worry about controlfiles, redo logs and undo etc. Instead it is just made up of datafiles and tempfiles to handle it's own objects. This includes it's own data dictionary, containing information about only those objects that are specific to the PDB. From Oracle 12.2 onward a PDB can, and should, have a local undo tablespace.

This split of the data dictionary between common objects, in the root container, and PDB-specific objects, in the PDB's data dictionary, is very important, because this separation is what gives the multitenant option its flexibility. From the perspective of the PDB, the data dictionary is the union of the root and PDB data dictionaries, so internally the PDB feels very much like a normal Oracle database. For example, the DBA_% and ALL_% views within the PDB appears the same as any non-CDB database.

Oracle Managed Files (OMF) and Multitenant

Oracle recommend the use of Oracle Managed Files (OMF) when using the multitenant architecture, as it simplifies a number of pieces of functionality. It seems the use of OMF is mandatory for some functionality, like the Application Containers functionality in Oracle 12.2.

Creating Pluggable Databases (PDBs)

Since the bulk of the working parts are already present in the root container, creating a new PDB is a comparatively quick and simple task. When creating a completely new PDP, the PDB is created as a copy of a seed PDB, so it only takes as long as the files take to copy.

Instead of creating a new PDB from the seed, you can clone an existing PDB.

It is also possible to create clones in a remote CDB.

Unpluging and Plugging in Pluggable Databases (PDBs)

One of the most powerful features of the multitenant option is the ability to unplug a PDB from a CDB and plug it back into another CDB.

Conversion of a non-CDB database to a pluggable database involves getting a description the non-CDB database and using this to plug it into a CDB as a new PDB.

Relocate a Pluggable Database (PDB)

From Oracle 12.2 onward it is possible to relocate a PDB, moving it from one CDB to another. This is significantly simpler than doing a conventional unplug/plugin.

Refreshable Pluggable Database (PDB)

From Oracle 12.2 onward it is possible to refresh a cloned PDB from the source PDB, provided it has only ever been opened in read-only mode.

Refreshable Pluggable Database (PDB) Switchover

From Oracle 18c onward it is possible to switchover a refreshable PDB.

Proxy Pluggable Database (PDB)

From Oracle 12.2 onward it is possible to create proxy PDB, which is a skeleton PDB that sends SQL across to a remote PDB to be processed. This allows you to have a local endpoint for a remote database.

Application Containers

Oracle 12.2 introduces the concept of application containers, which act like a mini-root container. They can be used to centralise shared configuration and applications, which are used by their dependent application PDBs.

Pluggable Database (PDB) Snapshot Carousel

From Oracle 18c onward it is possible to create automatically managed snapshots of a PDB, also know as a snapshot carousel.

Container Database (CDB) Fleet Management

From Oracle 18c onward it is possible to monitor multiple container databases centrally as a fleet.

Views

The introduction of the multitenant option brings with it an extra layer of data dictionary views, allowing reporting across the root container and the pluggable databases (PDBs). Ignoring editions for the moment, prior releases had the following hierarchy.

DBA_ : All objects in the database.

|

--ALL_ : Objects accessible by the current user, including those owned by the current user.

  |

  --USER_ : Objects owned by the current user.

With Oracle 12c, an extra layer is added to the hierarchy.

CDB_ : All objects in all containers * (root and all PDBs).

|

--DBA_ : All objects in the current container (root or PDB).

  |

  --ALL_ : Objects accessible by the current user in the current container (root or PDB), including those owned by the current user.

    |

    --USER_ : Objects owned by the current user in the current container (root or PDB).

The output of the CDB_ views is dependent on the container they are accessed from. When accessed from the root container, they do indeed present all the information from all the containers. When accessed from a PDB, they effectively act like the DBA_ views from within the container. This can be a little confusing at first.

Continue reading