Hello Ya'll: (All Ya'll is plural in Austin, Texas)

Recently, while consumin' a tuna sandwich (after one too many wings the evening before), I recently remembered a conversation with a client on a common test data dilemma.

As many of you may know, there are regulations from many IT disciplines and IT industry audit & compliance programs such as PCI that mandate that production data in not to be copied to test environments.

-- Here's a quick & dirty technique for obscuring the identity of real person names using DB2 SQL

-- The SQL below creates a DB2 table and insert 255 rows/names using common English surnames & English forenames that can be used for both boys and girls; i.e 'sometimes boys and girls are the same'; we will use this table in order to populate or obscure the identity of the real person.

-- If you repeat the 255 INSERTS coded below about 4 times,
-- you can then generate a random number to select between
-- one & 1000

-- See "SELECT DECIMAL(RAND() * 1000,7,0) AS RANDOM__SEQUENCE_NUMBER FROM SYSIBM.SYSDUMMY1 WITH UR ;" below.

-- The following will be the separators between each group:

-- (The reader is expected to copy the INSERT sql statements after each comment stmt below

-- The #1 - first set of inserts creates the first set of 255 rows/records toward the goal of 1000 - subtotal 255 rows.

-- The #2 - second set of inserts creates the next set of 255 rows/records toward the goal of 1000 - subtotal 510 rows.

-- The #3 - third set of inserts creates the next set of 255 rows/records toward the goal of 1000 - subtotal 765 rows.

-- The #4 - fourth set of inserts creates the next set of 255 rows/records toward the goal of 1000 - grand total 1020 rows.

-- Once the alex.people_names table is built,
-- we can generate a random number between one
-- and one-thousand to select a either a surname
-- or forename in order to populate or obscure the identity of
-- the real person; then select via 'name_sequence_nbr'
-- in order to get an obscured first or last name.

-- USING db2 -vtf popular-names1.SQL > popular-names1.txt

-- ############################################

-- popular-names1.sql

-- db2 -vtf popular-names1.SQL > popular-names1.txt

-- CONNECT TO SAMPLE ;
-- COMMIT ;
-- SELECT DECIMAL(RAND() * 1000,7,2) AS RANDOM__SEQUENCE_NUMBER FROM SYSIBM.SYSDUMMY1 WITH UR ;
-- SELECT DECIMAL(RAND() * 1000,7,0) AS RANDOM__SEQUENCE_NUMBER FROM SYSIBM.SYSDUMMY1 WITH UR ;

-- TERMINATE ;

-- ############################################

-- HERE'S SOME SAMPLE RESULTS OF THE DB2 SQL QUERY ABOVE:

-- CONNECT TO SAMPLE
-- Database Connection Information
-- Database server = DB2/NT 9.5.0
-- SQL authorization ID = ALEXANDE...
-- Local database alias = SAMPLE

-- COMMIT
-- DB20000I The SQL command completed successfully.

-- SELECT DECIMAL(RAND() * 1000,7,2) AS RANDOM__SEQUENCE_NUMBER FROM SYSIBM.SYSDUMMY1 WITH UR

-- RANDOM__SEQUENCE_NUMBER
-- -----------------------

-- 746.60

-- 1 record(s) selected.

-- SELECT DECIMAL(RAND() * 1000,7,0) AS RANDOM__SEQUENCE_NUMBER FROM SYSIBM.SYSDUMMY1 WITH UR

-- RANDOM__SEQUENCE_NUMBER
-- -----------------------

-- 174.

-- 1 record(s) selected.

-- TERMINATE
-- DB20000I The TERMINATE command completed successfully.

-- ############################################

-- popular-names1.SQL

-- db2 -vtf popular-names1.SQL > popular-names1.txt

connect to sample95 ;

select current timestamp from sysibm.sysdummy1 with ur ;

set current sqlid = 'alex' ;

-- This SQL below creates a DB2 sequence object named alex.name_nbr_sequence.

-- It can be used to generate a new sequential value using the 'next value for' clause in an Insert.

drop sequence alex.name_nbr_sequence ;
commit ;

create sequence alex.name_nbr_sequence
as integer
start with 1
increment BY 1
nomaxvalue
nocycle
cache 10;

commit ;

-- ############################################

drop table alex.people_names ;

commit ;

create table alex.people_names (name_sequence_nbr integer, sur_name_type CHAR(0, name_txt varchar(20), name_description varchar(30) ) ;

commit ;

-- ############################################

select count(*) from alex.people_names with ur ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Taylor', '10th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Campbell', '46th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Parker', '47th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Morgan', '57th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Bailey', '60th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Jordan', '110th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Hunter', '130th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Kennedy', '137th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Riley', '187th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Ryan', '203rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Reese', '372nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Quinn', '402nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Tyler', '415th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Logan', '454th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Carson', '472nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Casey', '479th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Chase', '508th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Cameron', '561st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Christian', '564th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Shannon', '679th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Avery', '775th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Hayden', '847th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Reilly', '901st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Emerson', '926th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Justice', '933rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Shea', '1,001st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Kendall', '1,017th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Madison', '1,094th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Payton', '1,239th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Drew', '1,301st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Ali', '1,733rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'London', '1,821st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Addison', '1,872nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Reagan', '2,000th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Rowan', '2,029th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Angel', '2,370th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Harley', '2,397th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'ashton', '2,432nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Sage', '3,241st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Peyton', '3,749th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Jaime', '4,933rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Jessie', '6,313rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'alex', '7,442nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'alexis', '8,677th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Guadalupe', '8,736th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Phoenix', '8,893rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Sidney', '10,979th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Dominique', '12,438th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Devin', '23,107th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Rylee', '25,146th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Kasey', '28,824th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Amari', '29,381st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Devon', '33,931st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Ariel', '58,728th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy-girl', 'Jamie', '63,085th most common surname') ;



select count(*) from alex.people_names with ur ;



insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Davis', '6th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Wilson', '8th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Anderson', '11th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Thomas', '12th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Jackson', '13th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Martin', '16th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Clark', '21st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Lewis', '23rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Lee', '24th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Walker', '25th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Allen', '27th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Scott', '34th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Nelson', '39th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Carter', '40th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Mitchell', '41st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Turner', '44th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Reed', '55th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Cooper', '62nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Howard', '65th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'James', '71st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Brooks', '73rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Bennett', '77th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Ross', '80th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Coleman', '82nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Perry', '84th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Bryant', '95th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'alexander', '96th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Russell', '97th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Griffin', '98th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Graham', '104th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Sullivan', '105th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Cole', '108th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Fisher', '113rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Ellis', '114th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Harrison', '115th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Cruz', '118th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Marshall', '119th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Tucker', '128th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Porter', '129th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Henry', '133rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Mason', '135th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Warren', '138th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Gordon', '143rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Grant', '154th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Stone', '158th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Hudson', '162nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Spencer', '163rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Pierce', '167th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Arnold', '170th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Ray', '173rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Duncan', '177th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Bradley', '181st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Lane', '182nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Lawrence', '192nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Elliott', '193rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Austin', '196th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Franklin', '199th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Lawson', '200th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Oliver', '210th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Stanley', '228th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'George', '230th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Reid', '232nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Dean', '236th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Gilbert', '237th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Garrett', '238th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Douglas', '257th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Terry', '265th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Wade', '267th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Curtis', '270th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Barrett', '280th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Gregory', '284th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Lucas', '286th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Miles', '287th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Craig', '288th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Fletcher', '293rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Vaughn', '305th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Dawson', '307th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Santiago', '308th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Chandler', '322nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Leonard', '324th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Daniel', '334th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Santos', '347th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Tate', '348th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Dennis', '355th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Cohen', '363rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Todd', '368th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Cannon', '373rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Joseph', '384th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Francis', '385th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Conner', '393rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'norman', '396th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Blake', '403rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Maxwell', '404th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Paul', '408th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Simon', '428th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Houston', '434th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Roy', '446th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Brady', '448th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Brock', '451st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'boy ', 'Frank', '453rd most common surname') ;



select count(*) from alex.people_names with ur ;



insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Kelly', '74th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Rose', '157th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Harper', '185th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lindsey', '411st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Mckenzie', '490th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Luna', '492nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Allison', '495th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Miranda', '634th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lara', '678th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'ashley', '725th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Joyce', '748th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Haley', '750th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Whitney', '783rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Rosa', '865th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Delaney', '929th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lindsay', '974th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Hanna', '1,007th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Shirley', '1,016th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Reyna', '1,054th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Tracy', '1,082nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Tatum', '1,083rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Grace', '1,167th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Leslie', '1,244th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lacy', '1,263rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Courtney', '1,273rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Paige', '1,287th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Piper', '1,298th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lilly', '1,334th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Hannah', '1,336th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Meredith', '1,366th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Marin', '1,495th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Cassidy', '1,505th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Hadley', '1,561st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Mckenna', '1,603rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Stacy', '1,619th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Diamond', '1,648th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lacey', '1,682nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Anaya', '1,714th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Sierra', '1,780th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Hope', '1,800th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Presley', '1,825th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Regan', '2,067th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Mallory', '2,091st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Ivy', '2,097th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Ruth', '2,250th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Libby', '2,269th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Paris', '2,319th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Kelsey', '2,374th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Amaya', '2,383rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Joy', '2,509th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Mackenzie', '2,574th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Shelby', '2,637th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Rhea', '3,277th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Hazel', '3,280th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Laney', '3,418th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Stacey', '3,429th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Ruby', '3,478th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lea', '3,529th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Holly', '3,709th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Hailey', '3,862nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Estrella', '4,052nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Simone', '4,380th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Eden', '4,941st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Miracle', '5,301st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Angeles', '5,381st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Sandy', '5,477th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Summer', '5,643rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Haven', '5,783rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Maya', '5,815th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Marley', '5,949th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Sylvia', '5,977th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Tiffany', '6,103rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lucia', '6,117th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lillie', '6,170th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Abbey', '6,457th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Robin', '6,582nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Victoria', '6,829th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Scarlett', '6,951st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Ansley', '7,051st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Dana', '7,161st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Brooke', '7,225th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Frances', '7,411st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Stella', '7,573rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Clare', '7,620th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Carley', '7,692th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Macy', '7,894th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Aubrey', '8,009th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Kiley', '8,127th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Halle', '8,924th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Delia', '9,041st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lucy', '9,218th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Maria', '9,311st most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Gloria', '9,332nd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Faith', '9,706th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Carmen', '10,119th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Lesley', '10,335th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Raven', '10,578th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Danna', '10,644th most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Reina', '10,853rd most common surname') ;

insert into alex.people_names (name_sequence_nbr, sur_name_type, name_txt, name_description) values (next value for alex.name_nbr_sequence, 'girl ', 'Vivian', '10,963rd most common surname') ;



commit ;



select count(*) from alex.people_names WHERE sur_name_type in ('boy-girl') with ur ;
select count(*) from alex.people_names WHERE sur_name_type in ('girl ') with ur ;
select count(*) from alex.people_names WHERE sur_name_type in ('boy ') with ur ;
select count(*) from alex.people_names with ur ;



-- select * from alex.people_names with ur ;



commit ;


-- The #2 - second set of inserts creates the next set of 255 rows/records toward the goal of 1000 - subtotal 510 rows.



THE READER WILL REPEAT ALL THE 255 INSERTS SHOWN ABOVE HERE.


select count(*) from alex.people_names WHERE sur_name_type in ('boy-girl') with ur ;
select count(*) from alex.people_names WHERE sur_name_type in ('girl ') with ur ;
select count(*) from alex.people_names WHERE sur_name_type in ('boy ') with ur ;
select count(*) from alex.people_names with ur ;
select current timestamp from sysibm.sysdummy1 with ur ;

commit ;

-- The #3 - third set of inserts creates the next set of 255 rows/records toward the goal of 1000 - subtotal 765 rows.



THE READER WILL REPEAT ALL THE 255 INSERTS SHOWN ABOVE HERE.



select count(*) from alex.people_names WHERE sur_name_type in ('boy-girl') with ur ;
select count(*) from alex.people_names WHERE sur_name_type in ('girl ') with ur ;
select count(*) from alex.people_names WHERE sur_name_type in ('boy ') with ur ;
select count(*) from alex.people_names with ur ;
select current timestamp from sysibm.sysdummy1 with ur ;

commit ;

-- The #4 - fourth set of inserts creates the next set of 255 rows/records toward the goal of 1000 - grand total 1020 rows.



THE READER WILL REPEAT ALL THE 255 INSERTS SHOWN ABOVE HERE.


select count(*) from alex.people_names WHERE sur_name_type in ('boy-girl') with ur ;
select count(*) from alex.people_names WHERE sur_name_type in ('girl ') with ur ;
select count(*) from alex.people_names WHERE sur_name_type in ('boy ') with ur ;
select count(*) from alex.people_names with ur ;
select current timestamp from sysibm.sysdummy1 with ur ;

commit ;

TERMINATE ;

More Oracle Jumbalaya Magic - Jumbling/Obfuscating/Creating Oracle Test Data for Test Environments

Hello Ya'll: (All Ya'll is plural in Austin, Texas)

I was subjugated last week and bribed with a Tuna Sub with pickles; it's time to pay the piper. This time the post is directed to the Oracle database practitioners. So here's some more 'magic' recursive SQL for Jumbling/Obfuscating/Creating Oracle Data for Test Environments at the bottom of this post.

I have some simple sample Oracle Recursive query SQL that shows how you use it to load a table with test data at the bottom of the blog.

Warning . . . skip down to the the "SERIOUS SECTION Stuff" and avoid my drivel.

Here's the RAMBLING SECTION Stuff:

As an aside, I recently was questioned by a fellow about comparing different RDBMS SQL query techniques side by side. As a result, I spent several days thinking about how I create my blog entries and presentations.

I started musing & searching the internet and stumbled on the history of schadenfreude - a german word with direct translation in many languages except English that defines the word as the "malicious enjoyment of the misfortunes of others."

First of all, my motivation is simply hoping to help enlighten others and I hope that ya'll get a laugh or two; perhaps others may offer some insight and choose to comment. Recently, some attendees at one of my sessions paid me the highest compliment, they related that they are some of my obfuscations techniques for creating phony sample hotspot SSN's were now being used in a state government test environment. I've previously been in a bar in Vienna (they have no clocks and never close) and at about 3:30 AM a fellow from Belgium bought me a beer and related that my debugging techniques have saved him countless hours). Ya'll should remember that 'free beer' is the two most powerful words in the English language. For some reason, many Europeans have also related to me that 'free beer' is also just as powerful in their native tongues.

-- Here's interesting and fun geek text that I found recently:

Check out this text that I found interesting on http://dictionary.reference.com/browse/bigot

bigot
n. [common] A person who is religiously attached to a particular computer, language, operating system, editor, or other tool (see religious issues). Usually found with a specifier; thus, `cray bigot', `ITS bigot', `APL bigot', `VMS bigot', `Berkeley bigot'. Real bigots can be distinguished from mere partisans or zealots by the fact that they refuse to learn alternatives even when the march of time and/or technology is threatening to obsolete the favored tool.

It is truly said "You can tell a bigot, but you can't tell him much." Compare weenie, Amiga Persecution Complex.
Jargon File 4.2.0 - The Free On-line Dictionary of Computing, © 1993-2007 Denis Howe

Amiga Persecution Complex
n. The disorder suffered by a particularly egregious variety of bigot, those who believe that the marginality of their preferred machine is the result of some kind of industry-wide conspiracy (for without a conspiracy of some kind, the eminent superiority of their beloved shining jewel of a platform would obviously win over all, market pressures be damned Those afflicted are prone to engaging in flame wars and calling for boycotts and mailbombings. Amiga Persecution Complex is by no means limited to Amiga users; NeXT, News, OS/2, Macintosh, LISP, and GNU users are also common victims. Linux users used to display symptoms very frequently before Linux started winning; some still do. See also newbie, troll, holy wars, weenie, Get a life!.

-- Here's more quotables:
http://forum.quoteland.com/1/OpenTopic?a=tpc&s=586192041&f=099191541&m=3571921701

Even a fool, when he holdeth his peace, is counted wise: and he that shutteth his lips is esteemed a man of understanding. -- Bible, 'Proverbs' 17:28
It is better to remain silent and be thought a fool, than to open your mouth and remove all doubt. -- George Eliot
Indeed, the Church wanted Galileo to say precisely this, that this was a mere supposition for convenience of calculation (a "hypothesis" in the parlance of the time). Galileo refused to say this--he thought it was a literal and absolute truth. General relativity says Galileo was wrong. (But it likewise says that it is wrong to say that the sun literally and absolutely goes around the earth). It was he who once said, "Tis better to be thought the fool and remain silent, than speak and remove all doubt."
"Better to be thought a fool, then to open your mouth and remove all doubt." -– Samuel Clemens (aka Mark Twain)
"Its better to be thought a fool rather than to speak up and remove all doubt." -- Lincoln
The Foolish Monkey by Cam Watkins: Once upon a time, there was a colony of monkeys living in the jungle. One particular monkey was thought to be a fool, because he never talked. One day, the lion called a meeting of all the beasts of the jungle. The Lion opened the meeting by asking a question. The monkey immediately sat up and stated his opinion, and removed all doubt. Moral: Better to be thought a fool, than to open your mouth and remove all doubt.

Here's the SERIOUS SECTION Stuff:

Now back to some more stuff that all ya'll may find useful; (I hope that I'm not on anyone's weekly top ten bad list).

In Oracle, the “CONNECT BY” is sometimes known as the syntax/method to perform recursive SQL in Oracle. I found that “CONNECT BY” issues in Oracle are not well known. (I encourage ya'll to go to Metalink and search on “CONNECT BY” for yourself and see the latest stuff).

This is what I found these 23 issues recently searching on “CONNECT BY” on Metalink :
https://metalink.oracle.com/metalink/plsql/f?p=130:14:477721712523772424:::14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,401436.1,1,1,1,helvetica#CONNECTBY

Connect By / Hierarchical Queries:

Wrong results from CONNECT BY query on IOT with secondary index/es
Poor CONNECT BY plan
CONNECT BY performance degrades when temp space needed on disk
OERI from CONNECT BY and correlated START WITH columns
Dump expCheckExprEquiv from pipelined function in hierarchical query

Poor plan for CONNECT BY
Dump (evaopn2) from CONNECT BY
Dump in qknlazopn() from hierarchical query
OERI[qkacon:FJfsrwo] on executing hierarchical query
OERI[qctcte1] from query with CONNECT BY

OERI:[qkaconCompareOpn:dty] from CONNECT BY with HAVING clause
Wrong results from CONNECT BY query
Dump [evaopn2] on a CONNECT BY query
Wrong results for CONNECT BY using CONNECT_BY_ISLEAF
Wrong Results (missing rows) on CONNECT BY when using function-based index

Dump or Wrong Results with ANSI JOINS and CONNECT BY
OERI[qkacon:FJswrwo] from CONNECT BY query
Wrong results / OERI from cost based CONNECT BY
Hierarchical (CONNECT BY) query on IOT returns wrong results
OERI[qkshtQBGet:1] from CONNECT BY query

Slow or Wrong Results on CONNECT BY queries with constant predicates
Wrong Results with "Connect By with Filtering"
Wrong results from CONNECT BY query

-- ##################################
-- ##################################

This is I also found this searching “_optimizer_connect_by_cost_based” :

_optimizer_connect_by_cost_based

NOTE: This is an internal Oracle parameter.

Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.

Oracle 11.1.0:

Parameter Name: _optimizer_connect_by_cost_based

Description: use cost-based transformation for connect by

Type: BOOL Obsoleted: FALSE

Can ALTER SESSION: TRUE Can ALTER SYSTEM: IMMEDIATE

This parameter is not documented and should not be used. Search for more info about [ _optimizer_connect_by_cost_based ] on the Oracle FAQ.

-- ##################################
-- ##################################

-- Here's some real potatoes

-- Here's a simple sample Oracle Recursive query that can be used to load a table with test data.

-- ##################################
-- ##################################

First, perform, select count(*) from all_objects in order to see what is your high water mark counter.

Example:

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Apr 2 15:48:57 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter user-name: scott
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from all_objects ;

COUNT(*)
----------
50328

-- ##################################
-- ##################################

-- Next, we will use ROWNUM, (a pseudo-column in Oracle), in a recursive query to easily generate perhaps up to 50328 rows (via ALL_OBJECTS).

-- ALL_OBJECTS - It is the Oracle system table that describes all objects accessible to the current user.

-- NOTES: MOD(ROWNUM,10), causes col1 to be a single digit

-- NOTES: MOD(ROWNUM,100000), causes col1 to be a more than a single digit

-- ##################################
-- ##################################

-- NOTES: MOD(ROWNUM,10), causes col1 to be a single digit

-- sqlplus scott/tiger @A2.sql > A2.txt

select count(*) from all_objects ;

DROP TABLE ALEX.TBL2 ;

CREATE TABLE ALEX.TBL2
(
COL_1 INTEGER NOT NULL,
COL_2 VARCHAR2(2) NOT NULL,
COL_3 DATE NOT NULL
);
INSERT INTO TBL2 ( COL_1, COL_2, COL_3 )
SELECT
MOD(ROWNUM,10),
DBMS_RANDOM.STRING('U',2),
-- DBMS_RANDOM.RANDOM,
SYSDATE-500+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects ;

select * from ALEX.TBL2 ;

exit ;

-- =============================================
-- A2.txt Results:
-- =============================================

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Apr 2 16:12:25 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

COUNT(*)
----------
50330

Table dropped.

Table created.

50331 rows created.

COUNT(*)
----------
50331

COL_1 CO COL_3
---------- -- ---------
1 HV 12-JAN-09
2 AR 08-JUL-08
3 DK 20-MAR-09
4 SS 01-OCT-08
5 AH 18-JUN-07
6 QX 29-APR-07
7 UJ 03-AUG-07
8 EK 25-MAY-09
9 KE 02-JUN-08
0 KG 01-MAY-07
1 KF 25-JUN-08
.
.
.

50331 rows selected.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options


-- ##################################
-- ##################################

-- NOTES: MOD(ROWNUM,100000), causes col1 to be a more than a single digit

-- sqlplus scott/tiger @A3.sql > A3.txt

select count(*) from all_objects ;

DROP TABLE ALEX.TBL3 ;

CREATE TABLE ALEX.TBL3
(
COL_1 INTEGER NOT NULL,
COL_2 VARCHAR2(2) NOT NULL,
COL_3 DATE NOT NULL
);
INSERT INTO TBL3 ( COL_1, COL_2, COL_3 )
SELECT
MOD(ROWNUM,100000),
DBMS_RANDOM.STRING('U',2),
-- DBMS_RANDOM.RANDOM,
SYSDATE-500+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects ;
select * from ALEX.TBL3 ;
exit ;

-- =============================================
-- A3.txt Results:
-- =============================================

-- NOTES: MOD(ROWNUM,100000), causes col1 to be a more than a single digit

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Apr 2 16:27:06 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

COUNT(*)
----------
50331

Table dropped.
Table created.

50332 rows created.

COL_1 CO COL_3
---------- -- ---------
1 LJ 16-OCT-08
2 CK 15-JUN-07
3 QI 11-APR-09
4 XG 23-MAR-09
5 TN 21-MAY-07
6 FJ 26-JAN-09
7 JE 04-FEB-08
8 ZN 11-AUG-08
9 AL 24-JAN-07
10 PB 13-FEB-07
11 HW 11-APR-08

COL_1 CO COL_3
---------- -- ---------
12 MZ 08-FEB-09
13 HD 10-MAY-09
14 CF 15-AUG-07
15 JN 13-MAR-07
16 KB 21-APR-09
17 UE 15-FEB-08
18 UG 03-NOV-07
19 GL 27-JAN-09
20 TS 12-FEB-08
21 HX 31-MAY-07
22 JK 08-AUG-07
.
.
.

COL_1 CO COL_3
---------- -- ---------
50326 PW 26-FEB-09
50327 DH 13-APR-08
50328 SZ 28-SEP-07
50329 MQ 16-FEB-07
50330 IV 31-MAY-07
50331 WJ 05-MAR-09
50332 KW 29-JUL-07

50332 rows selected.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options


-- ##################################
-- ##################################

-- =============================================

Here a starter set of recursive SQL for DB2 LUW that has some quick & dirty samples that you can try to help get thru the learning process. I have a full power point presentation if ya'll are interested that does a step-by-step instructions.

Enjoy.

-- =============================================
-- a1.sql
-- =============================================

-- HELLO_WORLD Recursive DB2 SQL Samples and results

-- C:\Program Files\IBM\SQLLIB\BIN> db2 -vtf a1.sql > a1.txt

-- ###############################

-- WITH LEVELS(CONTINENT_ID, PROV_STATE_ID , CONTINENT_CNTRY_NAME) AS ( SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID WHERE CONTINENT_ID.CONTINENT_ID = -1 UNION ALL SELECT CHILD.CONTINENT_ID, CHILD.PROV_STATE_ID, CHILD.CONTINENT_CNTRY_NAME FROM LEVELS CONTINENT_ID, ADK.HELLO_WORLD CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT DISTINCT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS ORDER BY CONTINENT_ID, CONTINENT_CNTRY_NAME, PROV_STATE_ID WITH UR

-- CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME

-- ------------ ------------- ----------------------------------------

-- SQL0347W The recursive common TABLE expression "ADK.LEVELS" may contain an

-- infinite loop. SQLSTATE=01605

-- ###############################

-- SQL0952N Processing was cancelled due to an interrupt. SQLSTATE=57014

-- ###############################
-- ###############################

-- WITH RUNSTATS

-- ###############################
-- ###############################

CONNECT TO SAMPLE ;
COMMIT ;

SET CURRENT SQLID = 'ADK' ;

DROP TABLE ADK.AREA1 ;

CREATE TABLE ADK.AREA1 (PARENT INTEGER, ME INTEGER, AREA CHAR(20)) ;

INSERT INTO ADK.AREA1 VALUES (-1, 1, 'Global') ;
INSERT INTO ADK.AREA1 VALUES (1, 2, 'Europe') ;
INSERT INTO ADK.AREA1 VALUES (1, 3, 'America') ;
INSERT INTO ADK.AREA1 VALUES (1, 4, 'Asia') ;
INSERT INTO ADK.AREA1 VALUES (2, 5, 'Denmark') ;
INSERT INTO ADK.AREA1 VALUES (2, 6, 'Germany') ;
INSERT INTO ADK.AREA1 VALUES (2, 7, 'UK') ;
INSERT INTO ADK.AREA1 VALUES (4, 8, 'Thailand') ;
INSERT INTO ADK.AREA1 VALUES (4, 9, 'Japan') ;
INSERT INTO ADK.AREA1 VALUES (3, 10, 'Canada') ;
INSERT INTO ADK.AREA1 VALUES (3, 11, 'USA') ;
INSERT INTO ADK.AREA1 VALUES (3, 12, 'Mexico') ;
INSERT INTO ADK.AREA1 VALUES (5, 13, 'Sub - DK1') ;
INSERT INTO ADK.AREA1 VALUES (5, 14, 'Sub - DK2') ;
INSERT INTO ADK.AREA1 VALUES (4, 15, 'Malaysia') ;

RUNSTATS ON TABLE ADK.AREA1 WITH DISTRIBUTION AND DETAILED INDEXES ALL SHRLEVEL CHANGE ;
COMMIT ;

WITH LEVELS(PARENT, ME, AREA) AS
(SELECT PARENT, ME, AREA
FROM ADK.AREA1
WHERE AREA = 'Sub - DK1'
UNION ALL
SELECT PARENT.PARENT, PARENT.ME, PARENT.AREA
FROM ADK.AREA PARENT, LEVELS CHILD
WHERE PARENT.ME = CHILD.PARENT
)
SELECT PARENT, ME, AREA
FROM LEVELS WITH UR;

-- TERMINATE ;

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

DROP TABLE ADK.HELLO_WORLD ;
COMMIT ;

--DROP INDEX ADK.STATE_CI ;
-- COMMIT ;

-- REMEMBER TO 'BRISTLE' AT DDL SAMPLES WITHOUT RUNSTATS & CLUSTERING INDEX...

-- ###############################

-- CREATE SOME DDL Statements for TABLE "ADK"."HELLO_WORLD"

CREATE TABLE ADK.HELLO_WORLD (CONTINENT_ID INTEGER NOT NULL, PROV_STATE_ID INTEGER NOT NULL, CONTINENT_CNTRY_NAME CHARACTER(60) NOT NULL ) ;

-- CREATE SOME DDL Statements for INDEXES on TABLE "ADK"."HELLO_WORLD"

-- CREATE UNIQUE INDEX "ADK"."STATE_CI" ON "ADK"."HELLO_WORLD"
-- ("CONTINENT_ID" ASC,
-- "PROV_STATE_ID" ASC)
-- PCTFREE 10 CLUSTER ;

-- CREATE SOME DDL Statements for primary key on TABLE "ADK"."HELLO_WORLD"

ALTER TABLE "ADK"."HELLO_WORLD"
ADD PRIMARY KEY
( "CONTINENT_ID"
, "PROV_STATE_ID"
, "CONTINENT_CNTRY_NAME"
) ;
-- ###############################

DESCRIBE TABLE ADK.HELLO_WORLD ;
DESCRIBE INDEXES FOR TABLE ADK.HELLO_WORLD ;

-- ###############################

-- INSERT SOME DATA INTO THE TABLE HERE...

-- INSERT THE TOP OF THE CHART DATA ROW INTO THE TABLE HERE...

INSERT INTO ADK.HELLO_WORLD VALUES (-1, 1, 'HELLO_WORLD') ;

-- ###############################

-- INSERT SOME DETAIL DATA INTO THE TABLE HERE...

-- ###############################

INSERT INTO ADK.HELLO_WORLD VALUES (4, 13, 'Asia Japan') ;
INSERT INTO ADK.HELLO_WORLD VALUES (4, 14, 'Asia China') ;
INSERT INTO ADK.HELLO_WORLD VALUES (4, 15, 'Asia Vietnam') ;
INSERT INTO ADK.HELLO_WORLD VALUES (3, 9, 'Europe Czech Republic') ;
INSERT INTO ADK.HELLO_WORLD VALUES (3, 10, 'Europe Slovak Republic') ;
INSERT INTO ADK.HELLO_WORLD VALUES (3, 11, 'Europe Austria') ;
INSERT INTO ADK.HELLO_WORLD VALUES (3, 12, 'Europe Germany') ;
INSERT INTO ADK.HELLO_WORLD VALUES (2, 16, 'South America Brazil') ;
INSERT INTO ADK.HELLO_WORLD VALUES (2, 17, 'South America Peru') ;
INSERT INTO ADK.HELLO_WORLD VALUES (2, 18, 'South America Bolivia') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 5, 'North America Canada British Columbia') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 6, 'North America Canada Ontario') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 7, 'North America Canada Newfoundland') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 8, 'North America Canada Quebec') ;
INSERT INTO ADK.HELLO_WORLD VALUES (2, 19, 'North America USA Illinois') ;
INSERT INTO ADK.HELLO_WORLD VALUES (2, 20, 'North America USA California') ;
INSERT INTO ADK.HELLO_WORLD VALUES (19, 21, 'North America USA Illinois COOK COUNTY') ;
INSERT INTO ADK.HELLO_WORLD VALUES (20, 22, 'North America USA California ORANGE COUNTY') ;
INSERT INTO ADK.HELLO_WORLD VALUES (21, 23, 'North America USA Illinois COOK COUNTY Chicago') ;
INSERT INTO ADK.HELLO_WORLD VALUES (22, 24, 'North America USA California ORANGE COUNTY Anaheim') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 2, 'North America USA') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 3, 'North America Mexico') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 4, 'North America Cuba') ;
COMMIT WORK;

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

RUNSTATS ON TABLE ADK.HELLO_WORLD WITH DISTRIBUTION AND DETAILED INDEXES ALL SHRLEVEL CHANGE ;

COMMIT ;

SELECT * FROM ADK.HELLO_WORLD WITH UR ;

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

-- ###############################

-- Here's an easy 'top-down list' SQL example:

-- CREATE a "downstream" list: First, the "root" node, then PARENTs, then children, etc :

-- PREPARE A COMMON TABLE EXPRESSION (CTE) - This one is called "LEVELS" :

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

WITH LEVELS(CONTINENT_ID, PROV_STATE_ID , CONTINENT_CNTRY_NAME) AS
(
SELECT CONTINENT_ID.CONTINENT_ID,
CONTINENT_ID.PROV_STATE_ID,
CONTINENT_ID.CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD CONTINENT_ID
WHERE CONTINENT_ID.CONTINENT_ID = -1
UNION ALL
SELECT CHILD.CONTINENT_ID, CHILD.PROV_STATE_ID, CHILD.CONTINENT_CNTRY_NAME
FROM LEVELS CONTINENT_ID, ADK.HELLO_WORLD CHILD
WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID
)
SELECT DISTINCT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM LEVELS
ORDER BY CONTINENT_ID, CONTINENT_CNTRY_NAME, PROV_STATE_ID WITH UR ;

-- ###############################

--(PARENT INTEGER, ME INTEGER, AREA CHAR(20))

-- Show the way from the bottom to the top.
-- @@@@@@@@@@@@@@@@@@@@@@@@@@

-- Find the upper levels for "North America Cuba"

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS
(SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD
WHERE CONTINENT_CNTRY_NAME = 'North America Cuba'
UNION ALL
SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD
WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID
SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM LEVELS WITH UR ;

-- ###############################

-- Find the upper levels for "North America USA California"

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS
(SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD
WHERE CONTINENT_CNTRY_NAME = 'North America USA California'
UNION ALL
SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD
WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID
)
SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM LEVELS WITH UR ;

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@

-- Find the upper levels for "North America USA California ORANGE COUNTY"

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS
(SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD
WHERE CONTINENT_CNTRY_NAME = 'North America USA California ORANGE COUNTY'
UNION ALL
SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD
WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID
)
SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM LEVELS WITH UR ;

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@

-- Find the upper levels for "North America USA California ORANGE COUNTY Anaheim"

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS
(SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD
WHERE CONTINENT_CNTRY_NAME = 'North America USA California ORANGE COUNTY Anaheim'
UNION ALL
SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD
WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID
)
SELECT CONTINENT_ID , PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM LEVELS
ORDER BY CONTINENT_ID, CONTINENT_CNTRY_NAME, PROV_STATE_ID WITH UR
;

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

WITH LEVELS(PARENT, ME, AREA) AS
(SELECT PARENT, ME, AREA
FROM ADK.AREA1
WHERE AREA = 'Sub - DK1'
UNION ALL
SELECT PARENT.PARENT, PARENT.ME, PARENT.AREA
FROM ADK.AREA PARENT, LEVELS CHILD
WHERE PARENT.ME = CHILD.PARENT
)
SELECT PARENT, ME, AREA
FROM LEVELS WITH UR;

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

CONNECT RESET;

-- TERMINATE ;

-- ###############################
-- ###############################

-- WITHOUT RUNSTATS

-- ###############################
-- ###############################

CONNECT TO SAMPLE ;
COMMIT ;

SET CURRENT SQLID = 'ADK' ;

DROP TABLE ADK.HELLO_WORLD ;
COMMIT ;

-- CREATE SOME DDL Statements for TABLE "ADK"."HELLO_WORLD"

CREATE TABLE ADK.HELLO_WORLD (CONTINENT_ID INTEGER NOT NULL, PROV_STATE_ID INTEGER NOT NULL, CONTINENT_CNTRY_NAME CHARACTER(60) NOT NULL ) ;

-- CREATE SOME DDL Statements for INDEXES on TABLE "ADK"."HELLO_WORLD"

-- CREATE UNIQUE INDEX "ADK"."STATE_CI" ON "ADK"."HELLO_WORLD"
-- ("CONTINENT_ID" ASC,
-- "PROV_STATE_ID" ASC)
-- PCTFREE 10 CLUSTER ;

-- CREATE SOME DDL Statements for primary key on TABLE "ADK"."HELLO_WORLD"

ALTER TABLE "ADK"."HELLO_WORLD"
ADD PRIMARY KEY
( "CONTINENT_ID"
, "PROV_STATE_ID"
, "CONTINENT_CNTRY_NAME"
) ;

-- ###############################

DESCRIBE TABLE ADK.HELLO_WORLD ;
DESCRIBE INDEXES FOR TABLE ADK.HELLO_WORLD ;

-- ###############################

-- INSERT SOME DATA INTO THE TABLE HERE...
-- INSERT THE TOP OF THE CHART DATA ROW INTO THE TABLE HERE...

INSERT INTO ADK.HELLO_WORLD VALUES (-1, 1, 'HELLO_WORLD') ;

-- ###############################

-- INSERT SOME DETAIL DATA INTO THE TABLE HERE...

-- ###############################

INSERT INTO ADK.HELLO_WORLD VALUES (4, 13, 'Asia Japan') ;
INSERT INTO ADK.HELLO_WORLD VALUES (4, 14, 'Asia China') ;
INSERT INTO ADK.HELLO_WORLD VALUES (4, 15, 'Asia Vietnam') ;
INSERT INTO ADK.HELLO_WORLD VALUES (3, 9, 'Europe Czech Republic') ;
INSERT INTO ADK.HELLO_WORLD VALUES (3, 10, 'Europe Slovak Republic') ;
INSERT INTO ADK.HELLO_WORLD VALUES (3, 11, 'Europe Austria') ;
INSERT INTO ADK.HELLO_WORLD VALUES (3, 12, 'Europe Germany') ;
INSERT INTO ADK.HELLO_WORLD VALUES (2, 16, 'South America Brazil') ;
INSERT INTO ADK.HELLO_WORLD VALUES (2, 17, 'South America Peru') ;
INSERT INTO ADK.HELLO_WORLD VALUES (2, 18, 'South America Bolivia') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 5, 'North America Canada British Columbia') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 6, 'North America Canada Ontario') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 7, 'North America Canada Newfoundland') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 8, 'North America Canada Quebec') ;
INSERT INTO ADK.HELLO_WORLD VALUES (2, 19, 'North America USA Illinois') ;
INSERT INTO ADK.HELLO_WORLD VALUES (2, 20, 'North America USA California') ;
INSERT INTO ADK.HELLO_WORLD VALUES (19, 21, 'North America USA Illinois COOK COUNTY') ;
INSERT INTO ADK.HELLO_WORLD VALUES (20, 22, 'North America USA California ORANGE COUNTY') ;
INSERT INTO ADK.HELLO_WORLD VALUES (21, 23, 'North America USA Illinois COOK COUNTY Chicago') ;
INSERT INTO ADK.HELLO_WORLD VALUES (22, 24, 'North America USA California ORANGE COUNTY Anaheim') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 2, 'North America USA') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 3, 'North America Mexico') ;
INSERT INTO ADK.HELLO_WORLD VALUES (1, 4, 'North America Cuba') ;
COMMIT WORK;

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

--RUNSTATS ON TABLE ADK.HELLO_WORLD WITH DISTRIBUTION AND DETAILED INDEXES ALL SHRLEVEL CHANGE ;
COMMIT ;

SELECT * FROM ADK.HELLO_WORLD WITH UR ;

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

-- ###############################

-- Here's an easy 'top-down list' SQL example:

-- CREATE a "downstream" list: First, the "root" node, then PARENTs, then children, etc :

-- PREPARE A COMMON TABLE EXPRESSION (CTE) - This one is called "LEVELS" :

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

WITH LEVELS(CONTINENT_ID, PROV_STATE_ID , CONTINENT_CNTRY_NAME) AS
(
SELECT CONTINENT_ID.CONTINENT_ID,
CONTINENT_ID.PROV_STATE_ID,
CONTINENT_ID.CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD CONTINENT_ID
WHERE CONTINENT_ID.CONTINENT_ID = -1
UNION ALL
SELECT CHILD.CONTINENT_ID, CHILD.PROV_STATE_ID, CHILD.CONTINENT_CNTRY_NAME
FROM LEVELS CONTINENT_ID, ADK.HELLO_WORLD CHILD
WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID
)
SELECT DISTINCT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM LEVELS
ORDER BY CONTINENT_ID, CONTINENT_CNTRY_NAME, PROV_STATE_ID WITH UR ;

-- ###############################

--(PARENT INTEGER, ME INTEGER, AREA CHAR(20))

-- Show the way from the bottom to the top.

-- @@@@@@@@@@@@@@@@@@@@@@@@

-- Find the upper levels for "North America Cuba"

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS
(SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD
WHERE CONTINENT_CNTRY_NAME = 'North America Cuba'
UNION ALL
SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD
WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID
)
SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM LEVELS WITH UR ;

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@

-- Find the upper levels for "North America USA California"

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS
(SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD
WHERE CONTINENT_CNTRY_NAME = 'North America USA California'
UNION ALL
SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD
WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID
)
SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM LEVELS WITH UR ;

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@

-- Find the upper levels for "North America USA California ORANGE COUNTY"

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;
WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS
(SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD
WHERE CONTINENT_CNTRY_NAME = 'North America USA California ORANGE COUNTY'
UNION ALL
SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD
WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID
)
SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM LEVELS WITH UR ;

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@

-- Find the upper levels for "North America USA California ORANGE COUNTY Anaheim"

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS
(SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD
WHERE CONTINENT_CNTRY_NAME = 'North America USA California ORANGE COUNTY Anaheim'
UNION ALL
SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME
FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD
WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID
)
SELECT CONTINENT_ID , PROV_STATE_ID, CONTINENT_CNTRY_NAME
FROM LEVELS
ORDER BY CONTINENT_ID, CONTINENT_CNTRY_NAME, PROV_STATE_ID WITH UR
;

-- @@@@@@@@@@@@@@@@@@@@@@@@@@

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR ;

-- @@@@@@@@@@@@@@@@@@@@@@@@@@

TERMINATE ;


-- =============================================
-- =============================================
-- =============================================

-- RESULTS:

-- =============================================
-- =============================================
-- =============================================


CONNECT TO SAMPLE

Database Connection Information

Database server = DB2/NT 8.2.2

SQL authorization ID = ALEXANDE...

Local database alias = SAMPLE

COMMIT

DB20000I The SQL command completed successfully.

SET CURRENT SQLID = 'ADK'

DB20000I The SQL command completed successfully.

DROP TABLE ADK.AREA1

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:

SQL0204N "ADK.AREA1" is an undefined name. SQLSTATE=42704

CREATE TABLE ADK.AREA1 (PARENT INTEGER, ME INTEGER, AREA CHAR(20))

DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (-1, 1, 'Global')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (1, 2, 'Europe')
DB20000I The SQL command completed successfully.


INSERT INTO ADK.AREA1 VALUES (1, 3, 'America')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (1, 4, 'Asia')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (2, 5, 'Denmark')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (2, 6, 'Germany')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (2, 7, 'UK')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (4, 8, 'Thailand')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (4, 9, 'Japan')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (3, 10, 'Canada')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (3, 11, 'USA')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (3, 12, 'Mexico')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (5, 13, 'Sub - DK1')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (5, 14, 'Sub - DK2')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.AREA1 VALUES (4, 15, 'Malaysia')
DB20000I The SQL command completed successfully.

RUNSTATS ON TABLE ADK.AREA1 WITH DISTRIBUTION AND DETAILED INDEXES ALL SHRLEVEL CHANGE
DB20000I The RUNSTATS command completed successfully.

COMMIT
DB20000I The SQL command completed successfully.


WITH LEVELS(PARENT, ME, AREA) AS (SELECT PARENT, ME, AREA FROM ADK.AREA1 WHERE AREA = 'Sub - DK1' UNION ALL SELECT PARENT.PARENT, PARENT.ME, PARENT.AREA FROM ADK.AREA PARENT, LEVELS CHILD WHERE PARENT.ME = CHILD.PARENT ) SELECT PARENT, ME, AREA FROM LEVELS WITH UR

PARENT ME AREA
----------- ----------- --------------------
SQL0347W The recursive common table expression "ADK.LEVELS" may contain an infinite loop. SQLSTATE=01605

5 13 Sub - DK1

2 5 Denmark

1 2 Europe

-1 1 Global

4 record(s) selected with 1 warning messages printed.

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR
TS
--------------------------
2007-12-27-09.51.42.265000

1 record(s) selected.

DROP TABLE ADK.HELLO_WORLD
DB20000I The SQL command completed successfully.

COMMIT
DB20000I The SQL command completed successfully.

CREATE TABLE ADK.HELLO_WORLD (CONTINENT_ID INTEGER NOT NULL, PROV_STATE_ID INTEGER NOT NULL, CONTINENT_CNTRY_NAME CHARACTER(60) NOT NULL )
DB20000I The SQL command completed successfully.

ALTER TABLE "ADK"."HELLO_WORLD" ADD PRIMARY KEY ( "CONTINENT_ID" , "PROV_STATE_ID" , "CONTINENT_CNTRY_NAME" )
DB20000I The SQL command completed successfully.


DESCRIBE TABLE ADK.HELLO_WORLD

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
CONTINENT_ID SYSIBM INTEGER 4 0 No

PROV_STATE_ID SYSIBM INTEGER 4 0 No

CONTINENT_CNTRY_NAME SYSIBM CHARACTER 60 0 No

3 record(s) selected.


DESCRIBE INDEXES FOR TABLE ADK.HELLO_WORLD

Index Index Unique Number of
schema name rule columns
-------------- ------------------ -------------- --------------
SYSIBM SQL061227095142450 P 3

1 record(s) selected.


INSERT INTO ADK.HELLO_WORLD VALUES (-1, 1, 'HELLO_WORLD')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (4, 13, 'Asia Japan')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (4, 14, 'Asia China')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (4, 15, 'Asia Vietnam')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (3, 9, 'Europe Czech Republic')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (3, 10, 'Europe Slovak Republic')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (3, 11, 'Europe Austria')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (3, 12, 'Europe Germany')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (2, 16, 'South America Brazil')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (2, 17, 'South America Peru')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (2, 18, 'South America Bolivia')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (1, 5, 'North America Canada British Columbia')

DB20000I The SQL command completed successfully.


INSERT INTO ADK.HELLO_WORLD VALUES (1, 6, 'North America Canada Ontario')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (1, 7, 'North America Canada Newfoundland')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (1, 8, 'North America Canada Quebec')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (2, 19, 'North America USA Illinois')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (2, 20, 'North America USA California')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (19, 21, 'North America USA Illinois COOK COUNTY')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (20, 22, 'North America USA California ORANGE COUNTY')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (21, 23, 'North America USA Illinois COOK COUNTY Chicago')


DB20000I The SQL command completed successfully.


INSERT INTO ADK.HELLO_WORLD VALUES (22, 24, 'North America USA California ORANGE COUNTY Anaheim')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (1, 2, 'North America USA')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (1, 3, 'North America Mexico')
DB20000I The SQL command completed successfully.

INSERT INTO ADK.HELLO_WORLD VALUES (1, 4, 'North America Cuba')
DB20000I The SQL command completed successfully.

COMMIT WORK
DB20000I The SQL command completed successfully.

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR

TS
--------------------------
2007-12-27-09.51.42.687004
1 record(s) selected.

RUNSTATS ON TABLE ADK.HELLO_WORLD WITH DISTRIBUTION AND DETAILED INDEXES ALL SHRLEVEL CHANGE
DB20000I The RUNSTATS command completed successfully.

COMMIT
DB20000I The SQL command completed successfully.

SELECT * FROM ADK.HELLO_WORLD WITH UR

CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME
------------ ------------- ---------------------------------------------------------

-1 1 HELLO_WORLD

1 2 North America USA

1 3 North America Mexico

1 4 North America Cuba

1 5 North America Canada British Columbia

1 6 North America Canada Ontario

1 7 North America Canada Newfoundland

1 8 North America Canada Quebec

2 16 South America Brazil

2 17 South America Peru

2 18 South America Bolivia

2 19 North America USA Illinois

2 20 North America USA California

3 9 Europe Czech Republic

3 10 Europe Slovak Republic

3 11 Europe Austria

3 12 Europe Germany

4 13 Asia Japan

4 14 Asia China

4 15 Asia Vietnam

19 21 North America USA Illinois COOK COUNTY

20 22 North America USA California ORANGE COUNTY

21 23 North America USA Illinois COOK COUNTY Chicago

22 24 North America USA California ORANGE COUNTY Anaheim

24 record(s) selected.


SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR

TS
--------------------------
2007-12-27-09.51.42.703001

1 record(s) selected.

SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR
TS
--------------------------
2007-12-27-09.51.42.703002

1 record(s) selected.


WITH LEVELS(CONTINENT_ID, PROV_STATE_ID , CONTINENT_CNTRY_NAME) AS ( SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID WHERE CONTINENT_ID.CONTINENT_ID = -1 UNION ALL SELECT CHILD.CONTINENT_ID, CHILD.PROV_STATE_ID, CHILD.CONTINENT_CNTRY_NAME FROM LEVELS CONTINENT_ID, ADK.HELLO_WORLD CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT DISTINCT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS ORDER BY CONTINENT_ID, CONTINENT_CNTRY_NAME, PROV_STATE_ID WITH UR

CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME

------------ ------------- -----------------------------------------------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an


infinite loop. SQLSTATE=01605






-1 1 HELLO_WORLD


1 5 North America Canada British Columbia


1 7 North America Canada Newfoundland


1 6 North America Canada Ontario


1 8 North America Canada Quebec


1 4 North America Cuba


1 3 North America Mexico


1 2 North America USA


2 20 North America USA California


2 19 North America USA Illinois


2 18 South America Bolivia


2 16 South America Brazil


2 17 South America Peru


3 11 Europe Austria


3 9 Europe Czech Republic


3 12 Europe Germany


3 10 Europe Slovak Republic


4 14 Asia China


4 13 Asia Japan


4 15 Asia Vietnam


19 21 North America USA Illinois COOK COUNTY


20 22 North America USA California ORANGE COUNTY


21 23 North America USA Illinois COOK COUNTY Chicago


22 24 North America USA California ORANGE COUNTY Anaheim






24 record(s) selected with 1 warning messages printed.










SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR






TS


--------------------------


2007-12-27-09.51.42.718000








1 record(s) selected.










WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS (SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD WHERE CONTINENT_CNTRY_NAME = 'North America Cuba' UNION ALL SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS WITH UR






CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME


------------ ------------- ------------------------------------------------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an


infinite loop. SQLSTATE=01605






1 4 North America Cuba


-1 1 HELLO_WORLD






2 record(s) selected with 1 warning messages printed.










SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR






TS


--------------------------


2007-12-27-09.51.42.718003






1 record(s) selected.










WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS (SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD WHERE CONTINENT_CNTRY_NAME = 'North America USA California' UNION ALL SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS WITH UR






CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME


------------ ------------- ------------------------------------------------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an


infinite loop. SQLSTATE=01605






2 20 North America USA California


1 2 North America USA


-1 1 HELLO_WORLD






3 record(s) selected with 1 warning messages printed.










SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR






TS


--------------------------


2007-12-27-09.51.42.718006






1 record(s) selected.










WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS (SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD WHERE CONTINENT_CNTRY_NAME = 'North America USA California ORANGE COUNTY' UNION ALL SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS WITH UR






CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME


------------ ------------- ------------------------------------------------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an


infinite loop. SQLSTATE=01605






20 22 North America USA California ORANGE COUNTY


2 20 North America USA California


1 2 North America USA


-1 1 HELLO_WORLD






4 record(s) selected with 1 warning messages printed.










SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR






TS


--------------------------


2007-12-27-09.51.42.734002






1 record(s) selected.










WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS (SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD WHERE CONTINENT_CNTRY_NAME = 'North America USA California ORANGE COUNTY Anaheim' UNION ALL SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT CONTINENT_ID , PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS ORDER BY CONTINENT_ID, CONTINENT_CNTRY_NAME, PROV_STATE_ID WITH UR






CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME


------------ ------------- ------------------------------------------------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an


infinite loop. SQLSTATE=01605






-1 1 HELLO_WORLD


1 2 North America USA


2 20 North America USA California


20 22 North America USA California ORANGE COUNTY


22 24 North America USA California ORANGE COUNTY Anaheim






5 record(s) selected with 1 warning messages printed.










SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR






TS


--------------------------


2007-12-27-09.51.42.734005






1 record(s) selected.










WITH LEVELS(PARENT, ME, AREA) AS (SELECT PARENT, ME, AREA FROM ADK.AREA1 WHERE AREA = 'Sub - DK1' UNION ALL SELECT PARENT.PARENT, PARENT.ME, PARENT.AREA FROM ADK.AREA PARENT, LEVELS CHILD WHERE PARENT.ME = CHILD.PARENT ) SELECT PARENT, ME, AREA FROM LEVELS WITH UR






PARENT ME AREA


----------- ----------- --------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an


infinite loop. SQLSTATE=01605






5 13 Sub - DK1


2 5 Denmark


1 2 Europe


-1 1 Global






4 record(s) selected with 1 warning messages printed.










SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR






TS


--------------------------


2007-12-27-09.51.42.750000






1 record(s) selected.










CONNECT RESET


DB20000I The SQL command completed successfully.






CONNECT TO SAMPLE






Database Connection Information






Database server = DB2/NT 8.2.2


SQL authorization ID = ALEXANDE...


Local database alias = SAMPLE










COMMIT


DB20000I The SQL command completed successfully.






SET CURRENT SQLID = 'ADK'


DB20000I The SQL command completed successfully.






DROP TABLE ADK.HELLO_WORLD


DB20000I The SQL command completed successfully.






COMMIT


DB20000I The SQL command completed successfully.






CREATE TABLE ADK.HELLO_WORLD (CONTINENT_ID INTEGER NOT NULL, PROV_STATE_ID INTEGER NOT NULL, CONTINENT_CNTRY_NAME CHARACTER(60) NOT NULL )


DB20000I The SQL command completed successfully.






ALTER TABLE "ADK"."HELLO_WORLD" ADD PRIMARY KEY ( "CONTINENT_ID" , "PROV_STATE_ID" , "CONTINENT_CNTRY_NAME" )


DB20000I The SQL command completed successfully.






DESCRIBE TABLE ADK.HELLO_WORLD






Column Type Type


name schema name Length Scale Nulls


------------------------------ --------- ------------------ -------- ----- ------


CONTINENT_ID SYSIBM INTEGER 4 0 No


PROV_STATE_ID SYSIBM INTEGER 4 0 No


CONTINENT_CNTRY_NAME SYSIBM CHARACTER 60 0 No






3 record(s) selected.










DESCRIBE INDEXES FOR TABLE ADK.HELLO_WORLD






Index Index Unique Number of


schema name rule columns


-------------- ------------------ -------------- --------------


SYSIBM SQL061227095143210 P 3






1 record(s) selected.










INSERT INTO ADK.HELLO_WORLD VALUES (-1, 1, 'HELLO_WORLD')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (4, 13, 'Asia Japan')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (4, 14, 'Asia China')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (4, 15, 'Asia Vietnam')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (3, 9, 'Europe Czech Republic')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (3, 10, 'Europe Slovak Republic')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (3, 11, 'Europe Austria')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (3, 12, 'Europe Germany')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (2, 16, 'South America Brazil')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (2, 17, 'South America Peru')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (2, 18, 'South America Bolivia')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (1, 5, 'North America Canada British Columbia')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (1, 6, 'North America Canada Ontario')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (1, 7, 'North America Canada Newfoundland')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (1, 8, 'North America Canada Quebec')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (2, 19, 'North America USA Illinois')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (2, 20, 'North America USA California')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (19, 21, 'North America USA Illinois COOK COUNTY')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (20, 22, 'North America USA California ORANGE COUNTY')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (21, 23, 'North America USA Illinois COOK COUNTY Chicago')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (22, 24, 'North America USA California ORANGE COUNTY Anaheim')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (1, 2, 'North America USA')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (1, 3, 'North America Mexico')


DB20000I The SQL command completed successfully.






INSERT INTO ADK.HELLO_WORLD VALUES (1, 4, 'North America Cuba')


DB20000I The SQL command completed successfully.






COMMIT WORK


DB20000I The SQL command completed successfully.






SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR






TS


--------------------------


2007-12-27-09.51.43.453002






1 record(s) selected.










COMMIT


DB20000I The SQL command completed successfully.






SELECT * FROM ADK.HELLO_WORLD WITH UR






CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME


------------ ------------- ------------------------------------------------------------


-1 1 HELLO_WORLD


4 13 Asia Japan


4 14 Asia China


4 15 Asia Vietnam


3 9 Europe Czech Republic


3 10 Europe Slovak Republic


3 11 Europe Austria


3 12 Europe Germany


2 16 South America Brazil


2 17 South America Peru


2 18 South America Bolivia


1 5 North America Canada British Columbia


1 6 North America Canada Ontario


1 7 North America Canada Newfoundland


1 8 North America Canada Quebec


2 19 North America USA Illinois


2 20 North America USA California


19 21 North America USA Illinois COOK COUNTY


20 22 North America USA California ORANGE COUNTY


21 23 North America USA Illinois COOK COUNTY Chicago


22 24 North America USA California ORANGE COUNTY Anaheim


1 2 North America USA


1 3 North America Mexico


1 4 North America Cuba


24 record(s) selected.



SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR



TS


--------------------------


2007-12-27-09.51.43.453004



1 record(s) selected.



SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR



TS

--------------------------


2007-12-27-09.51.43.453005


1 record(s) selected.


WITH LEVELS(CONTINENT_ID, PROV_STATE_ID , CONTINENT_CNTRY_NAME) AS ( SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID WHERE CONTINENT_ID.CONTINENT_ID = -1 UNION ALL SELECT CHILD.CONTINENT_ID, CHILD.PROV_STATE_ID, CHILD.CONTINENT_CNTRY_NAME FROM LEVELS CONTINENT_ID, ADK.HELLO_WORLD CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT DISTINCT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS ORDER BY CONTINENT_ID, CONTINENT_CNTRY_NAME, PROV_STATE_ID WITH UR



CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME

------------ ------------- ------------------------------------------------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an infinite loop. SQLSTATE=01605



-1 1 HELLO_WORLD


1 5 North America Canada British Columbia


1 7 North America Canada Newfoundland


1 6 North America Canada Ontario


1 8 North America Canada Quebec


1 4 North America Cuba


1 3 North America Mexico


1 2 North America USA


2 20 North America USA California


2 19 North America USA Illinois


2 18 South America Bolivia


2 16 South America Brazil


2 17 South America Peru


3 11 Europe Austria


3 9 Europe Czech Republic


3 12 Europe Germany


3 10 Europe Slovak Republic


4 14 Asia China


4 13 Asia Japan


4 15 Asia Vietnam


19 21 North America USA Illinois COOK COUNTY


20 22 North America USA California ORANGE COUNTY


21 23 North America USA Illinois COOK COUNTY Chicago


22 24 North America USA California ORANGE COUNTY Anaheim


24 record(s) selected with 1 warning messages printed.



SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR




TS

--------------------------


2007-12-27-09.51.43.468001



1 record(s) selected.




-- WITHOUT RUNSTATS:

2007-12-27-09.51.43.468001


2007-12-27-09.51.43.453005

========

14996


-- WITH RUNSTATS:

2007-12-27-09.51.42.718000


2007-12-27-09.51.42.703002

========

14998




WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS (SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD WHERE CONTINENT_CNTRY_NAME = 'North America Cuba' UNION ALL SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS WITH UR



CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME


------------ ------------- ------------------------------------------------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an infinite loop. SQLSTATE=01605



1 4 North America Cuba


-1 1 HELLO_WORLD



2 record(s) selected with 1 warning messages printed.



SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR


TS

--------------------------


2007-12-27-09.51.43.468004



1 record(s) selected.



WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS (SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD WHERE CONTINENT_CNTRY_NAME = 'North America USA California' UNION ALL SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS WITH UR


CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME


------------ ------------- ------------------------------------------------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an infinite loop. SQLSTATE=01605



2 20 North America USA California


1 2 North America USA


-1 1 HELLO_WORLD



3 record(s) selected with 1 warning messages printed.










SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR






TS


--------------------------


2007-12-27-09.51.43.484001






1 record(s) selected.










WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS (SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD WHERE CONTINENT_CNTRY_NAME = 'North America USA California ORANGE COUNTY' UNION ALL SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS WITH UR



CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME


------------ ------------- ------------------------------------------------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an infinite loop. SQLSTATE=01605



20 22 North America USA California ORANGE COUNTY


2 20 North America USA California


1 2 North America USA


-1 1 HELLO_WORLD



4 record(s) selected with 1 warning messages printed.



SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR



TS


--------------------------


2007-12-27-09.51.43.484004




1 record(s) selected.




WITH LEVELS(CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME) AS (SELECT CONTINENT_ID, PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD WHERE CONTINENT_CNTRY_NAME = 'North America USA California ORANGE COUNTY Anaheim' UNION ALL SELECT CONTINENT_ID.CONTINENT_ID, CONTINENT_ID.PROV_STATE_ID, CONTINENT_ID.CONTINENT_CNTRY_NAME FROM ADK.HELLO_WORLD CONTINENT_ID, LEVELS CHILD WHERE CONTINENT_ID.PROV_STATE_ID = CHILD.CONTINENT_ID ) SELECT CONTINENT_ID , PROV_STATE_ID, CONTINENT_CNTRY_NAME FROM LEVELS ORDER BY CONTINENT_ID, CONTINENT_CNTRY_NAME, PROV_STATE_ID WITH UR



CONTINENT_ID PROV_STATE_ID CONTINENT_CNTRY_NAME


------------ ------------- ------------------------------------------------------------


SQL0347W The recursive common table expression "ADK.LEVELS" may contain an


infinite loop. SQLSTATE=01605






-1 1 HELLO_WORLD


1 2 North America USA


2 20 North America USA California


20 22 North America USA California ORANGE COUNTY


22 24 North America USA California ORANGE COUNTY Anaheim


5 record(s) selected with 1 warning messages printed.



SELECT CURRENT TIMESTAMP AS TS FROM SYSIBM.SYSDUMMY1 WITH UR



TS


--------------------------


2007-12-27-09.51.43.500000


1 record(s) selected.



TERMINATE


DB20000I The TERMINATE command completed successfully.

Sometimes "RDBMS VIEWS" & "VIEW SQL" seem to resemble the 'work behind the curtain' in the mighty land of OZ.

A fellow database practitioner asked recently "Where are VIEWS stored in DB2 LUW" as they had found a VIEW SQL statement or two that was killing both their CPU & I/O rates.

Many of their VIEWS were returning 2000 rows or more every time bypassing without any "FETCH limiting SQL" in their "C# & JAVA WIENIE" coded applications.

Their initial review showed an issue commonly faced by our fellow database practitioners.

They had a small amount (633) executions that were using 10.7% of the overall CPU time while fetching/returning a whopping average 104,285,598 rows each time; (whereas about 10 rows fetched/returned on average are normally sufficient).

They surmised that they also had more VIEW statements with some "LIKE" SQL where the top 300 rows returned would have been sufficient; (you can limit in DB2 SQL by using "fetch first 300 rows only" in the SQL and not by needlessly fetching, then dropping/kicking out the extra 1700 rows in the application code) by simply appending "FETCH FIRST 300 ROWS ONLY" to the SELECT DML SQL statement.

The VIEW SQl statement in question also used 45.57% of all Sort time with 1266 sorts (double the number executions).

(Off the cuff, "Sort Counts" that are "equal or double SQL executions" are usually a great candidates for a DB2 Clustering index - and possibly a "allow reverse scans" clause. My experience helping fellow database practitioners solve issues frequently involve databases with 1,000+ tables. I generally find zero (0) occurrences of these two things: Both "CLUSTER" is missing from all/any indexes and "ALLOW REVERSE SCANS" is also missing from any/all of their indexes.)

(Ya'll should remember that in DB2 LUW V9.1: "allow reverse scans" clause is now enabled by default for indexes, primary keys and unique keys. Many recurrent problems that we frequently help fellow database practitioners solve involve databases created prior to V9.1; zero (0) occurrences of "CLUSTER"and "allow reverse scans" in their indexes is common place for poor performing databases.)

Lately, I've been doing a fair amount of science experiments on both Oracle and DB2 LUW, I have views on 'VIEWS ON VIEWS ON VIEWS' (THERE WILL BE ADDITIONAL BLOG COMMENTARY ON ANOTHER DAY); But for now, here's the reply to my fellow database practitioner:


The DDL FOR VIEWS IS STORED IN THE ?VIEW_DEFINITION? COLUMN IN SYSIBM.VIEWS :


C:\Program Files\IBM\SQLLIB\BIN> db2 connect to sample

C:\Program Files\IBM\SQLLIB\BIN> db2 describe table sysibm.views

Data type Column

Column name schema Data type name Length Scale Nulls

------------------------------- --------- ------------------- ---------- ----- ------

TABLE_CATALOG SYSIBM VARCHAR 128 0 No

TABLE_SCHEMA SYSIBM VARCHAR 128 0 No

TABLE_NAME SYSIBM VARCHAR 128 0 No


VIEW_DEFINITION SYSIBM CLOB 2097152 0 No


CHECK_OPTION SYSIBM VARCHAR 8 0 Yes

IS_UPDATABLE SYSIBM VARCHAR 3 0 Yes


6 record(s) selected.


-- ========================================================

-- Here?s some science experiment/command line junkie SQL examples for you to try yourself:

--C:\Program Files\IBM\SQLLIB\BIN>

db2 SELECT VIEW_DEFINITION FROM SYSIBM.VIEWS where VIEW_DEFINITION LIKE 'CREATE VIEW%' WITH UR >> A.TXT

-- C:\Program Files\IBM\SQLLIB\BIN>
db2 SELECT TABLE_NAME, TABLE_SCHEMA, VIEW_DEFINITION FROM SYSIBM.VIEWS where VIEW_DEFINITION LIKE '% SELECT ALL PublisherKey, Title %' WITH UR >> A.TXT

-- C:\Program Files\IBM\SQLLIB\BIN>

db2 SELECT TABLE_NAME, TABLE_SCHEMA, VIEW_DEFINITION FROM SYSIBM.VIEWS where VIEW_DEFINITION LIKE '%PublisherKey, Title%' WITH UR >> A.TXT

-- ========================================================

You will notice that the beginning of VIEW_DEFINITION CLOB column has the 'CREATE VIEW schema & viewname.

For example replace ?PublisherKey, Title? with your errant VIEW SQL as the search argument.

(BTW - TABLE_CATALOG HOLDS A DATABASE NAME)

---------------------------------------------------------------------------


EXAMPLE:

db2 SELECT TABLE_NAME, TABLE_SCHEMA, VIEW_DEFINITION FROM SYSIBM.VIEWS where VIEW_DEFINITION LIKE '%PublisherKey, Title%' WITH UR >> A.TXT

-- RETURNS THE FOLLOWING:

TABLE_NAME TABLE_SCHEMA VIEW_DEFINITION
.
.
.
---------------------------------------------------------------------------

BOOKV1 DBIPROD CREATE VIEW DBIPROD.BOOKV1 AS SELECT ALL PublisherKey, Title , PublisherNo FROM DBIPROD.BOOK

1 record(s) selected.

Hope this helps someone !

Do you have a VIEW on my VIEW ?

Reply to Alexander . Kopac at database-brothers . com

Recursive SQL for Mining the DB2 Catalog Views and Tables - 2 of 2

Hello Ya'll once again: (All Ya'll is plural in Austin, Texas)

Having survived the many casino's and the 4 hour border crossing wait in the rain on the pretty drive between Seattle & Vancouver, British Columbia, I had just enough gas money to get back to the Seattle airport after BMC Userworld Conference 2007 where I spoke about 'Database Auditing for Compliance, Security,& Reputation Management? on Nov 2.

This week we were invited to celebrate yet another proverbial free lunch (DBA food = pizza u pivo) and (again under the influence of the spirits or the tomato sauce), I promised that I would get my blog updated.

What follows is still perhaps weakly described as ?DB2 LUW System Catalog - The Magical Table Views?!

As many of you know, DBA's are routinely handed a Legacy application without benefit of the Logical or Physical model and asked to migrate the system across platforms such as Windows to AIX or Sun Solaris etc.

In addition, there's many 'after thoughts where an existing table needs to broken up or massively changed to fix up either a poor design or to add some petty and 'minor' new business functionality like 24X7 web enablement!

The SQL coding technique that follows below can be used to help initially understand the big picture for application tables within the DB2 System Catalog. Use my example below and simply change the 'EXPLAIN_INSTANCE' value to your table name, then look for the magic !

Here's two real life example from the DB2 LUW System Catalog: (Cut/paste and run for yourself

For example, I picked two EXPLAIN tables - SYSIBM.EXPLAIN_INSTANCE & SYSIBM.EXPLAIN_DIAGNOSTIC tables.

BTW, here's some notes for the following SQL Catalog query:

ADK.CHILDREN table is a CTE (Common Table Expression) i.e. a Temporary Table that is generated/created on the fly just for the duration of this query.

=================================================================
=================================================================

-- FIND PARENTS & CHILDREN OF A SPECIFIC TABLE (UP TO 20 LEVELS)

=================================================================
=================================================================

SET CURRENT SQLID = 'ADK' ;

WITH ADK.CHILDREN ( KKEY, LEVEL) AS (SELECT REFTBNAME, 1 FROM SYSIBM.SYSRELS WHERE TBNAME = 'EXPLAIN_INSTANCE' UNION ALL SELECT H.REFTBNAME, CC.LEVEL + 1 FROM SYSIBM.SYSRELS H ,ADK.CHILDREN CC WHERE H.TBNAME = CC.KKEY AND CC.LEVEL < 20 ) ,PARENTS (KKEY, LEVEL) AS (SELECT TBNAME, -1 FROM SYSIBM.SYSRELS WHERE REFTBNAME = 'EXPLAIN_INSTANCE' UNION ALL SELECT H.TBNAME, P.LEVEL - 1 FROM SYSIBM.SYSRELS H ,PARENTS P WHERE H.REFTBNAME = P.KKEY AND P.LEVEL > -20 ) SELECT KKEY, LEVEL FROM ADK.CHILDREN UNION ALL SELECT KKEY, LEVEL FROM PARENTS WITH UR


KKEY LEVEL

----------------------------------- -----------

EXPLAIN_STATEMENT -1

EXPLAIN_ARGUMENT -2

EXPLAIN_OBJECT -2

EXPLAIN_OPERATOR -2

EXPLAIN_PREDICATE -2

EXPLAIN_STREAM -2

EXPLAIN_DIAGNOSTIC -2

EXPLAIN_DIAGNOSTIC_DATA -3

8 record(s) selected.

=================================================================
=================================================================

SET CURRENT SQLID = 'ADK' ;

WITH ADK.CHILDREN ( KKEY, LEVEL) AS (SELECT REFTBNAME, 1 FROM SYSIBM.SYSRELS WHERE TBNAME = 'EXPLAIN_DIAGNOSTIC' UNION ALL SELECT H.REFTBNAME, CC.LEVEL + 1 FROM SYSIBM.SYSRELS H ,ADK.CHILDREN CC WHERE H.TBNAME = CC.KKEY AND CC.LEVEL < 20 ) ,PARENTS (KKEY, LEVEL) AS (SELECT TBNAME, -1 FROM SYSIBM.SYSRELS WHERE REFTBNAME = 'EXPLAIN_DIAGNOSTIC' UNION ALL SELECT H.TBNAME, P.LEVEL - 1 FROM SYSIBM.SYSRELS H ,PARENTS P WHERE H.REFTBNAME = P.KKEY AND P.LEVEL > -20 ) SELECT KKEY, LEVEL FROM ADK.CHILDREN UNION ALL SELECT KKEY, LEVEL FROM PARENTS WITH UR

KKEY LEVEL

----------------------------------- -----------

EXPLAIN_DIAGNOSTIC_DATA -1

EXPLAIN_STATEMENT 1

EXPLAIN_INSTANCE 2

3 record(s) selected.

=================================================================
=================================================================

The views expressed are my own and not necessarily those of Database-Brothers (DBI) and its affiliates.

The views and opinions expressed by visitors for this here blog are theirs and do not necessarily reflect my own.

I've been using databases for far too long; working way too hard and spending too little time on golf and spending far too much time working on computers since before invention of ...

I welcome any comments to Alexander.Kopac@database-brothers.com

=================================================================

Recursive SQL for Mining the DB2 Catalog Views and Tables 1 of 2

Hello Ya'll: (All Ya'll is plural in Austin, Texas)

As some of you may know, my official title at DBI' for the past several years has been "Database Magician".

Recently I had some folks from Austin say that they love my business card title: ?Database Magician? ? How do you get a gig like that they asked ? Sometimes it feels like pulling rabbits out of hats?" Isn't that the perfect title for all DBA's ? "It takes '5 years to create an issue' and the expectation is often 'get it fixed in 5 minutes."

I once had a boss that said "... you DBA's are just obstacles, you're just a bunch of over-priced whiners'. . . but that's a story for another day at an IDUG, IOD, AOUG, OOW event conference during a happy hour.

This week, my co-workers were all encouraged to wear our Halloween costumes. Fortunately for me, my costume search was easy ? my Magician?s hat sits on top of my desk; seldom far from sight.

To go along with this week's theme, here's some more 'magic' recursive SQL for mining the relationships of the system catalog Tables & Views; this time directed to the DB2 for LUW & DB2 for z/OS database practitioners.

Earlier this week, invited for yet another proverbial free lunch I again promised that I was to get my blog updated before I ran off to Vancouver, British Columbia and BMC Userworld Conference 2007 to speak about 'Database Auditing for Compliance, Security,& Reputation Management? on Nov 2.

Well, what follows is perhaps weakly described as ?DB2 LUW System Catalog - The Magical Views?!

As many of you know DBA's are handed a Legacy application without benefit of the Logical or Physical model and asked to migrate the system across platforms Windows to AIX or Sun Solaris etc.

In addition, many times, an existing table needs to broken up or massively changed to fix up either a poor design or to add some 'minor' new business functionality like 24X7 web enablement.

The SQL coding technique that I follows below can be used to help initially understand the big picture for application Views within the DB2 System Catalog.

Simply change the PARAMETERS literal value to your table name and look for the magic !

Here are some notes for the following SQL Catalog query:

ADK.CHILDREN table is a CTE (Common Table Expression) i.e. a Temporary Table that is generated/created on the fly just for the duration of this query.

==============================================================

-- FIND PARENTS & CHILDREN OF A SPECIFIC VIEW (UP TO 20 LEVELS)

==============================================================

SET CURRENT SQLID = 'ADK' ;

WITH ADK.CHILDREN (BCREATOR, VVIEW, LEVEL) AS

(SELECT BCREATOR, BNAME, 1

FROM SYSIBM.SYSVIEWDEP

WHERE DNAME = 'PARAMETERS'

UNION ALL

SELECT H.BCREATOR, H.BNAME, CC.LEVEL + 1

FROM SYSIBM.SYSVIEWDEP H

,ADK.CHILDREN CC

WHERE H.DNAME = CC.VVIEW

AND CC.LEVEL < 20 )

,PARENTS (BCREATOR, VVIEW, LEVEL) AS

(SELECT BCREATOR, DNAME, -1

FROM SYSIBM.SYSVIEWDEP

WHERE BNAME = 'PARAMETERS'

UNION ALL

SELECT H.BCREATOR, H.DNAME, P.LEVEL - 1

FROM SYSIBM.SYSVIEWDEP H

,PARENTS P

WHERE H.BNAME = P.VVIEW

AND P.LEVEL > -20 )

SELECT BCREATOR, VVIEW, LEVEL

FROM ADK.CHILDREN

UNION ALL

SELECT BCREATOR, VVIEW, LEVEL

FROM PARENTS WITH UR;

==============================================================

Here's a real life example from the DB2 LUW System Catalog: (Cut/paste and run for yourself

The SYSIBM.PARAMETERS_S shows as the parent view to SYSIBM.PARAMETERS (-1 value)

The children are SYSIBM.SYSTRANSFORMS SYSIBM.SYSROUTINES SYSIBM.SYSROUTINEPARMS

WITH ADK.CHILDREN (BCREATOR, VVIEW, LEVEL) AS (SELECT BCREATOR, BNAME, 1 FROM SYSIBM.SYSVIEWDEP WHERE DNAME = 'PARAMETERS' UNION ALL SELECT H.BCREATOR, H.BNAME, CC.LEVEL + 1 FROM SYSIBM.SYSVIEWDEP H ,ADK.CHILDREN CC WHERE H.DNAME = CC.VVIEW AND CC.LEVEL < 20 ) ,PARENTS (BCREATOR, VVIEW, LEVEL) AS (SELECT BCREATOR, DNAME, -1 FROM SYSIBM.SYSVIEWDEP WHERE BNAME = 'PARAMETERS' UNION ALL SELECT H.BCREATOR, H.DNAME, P.LEVEL - 1 FROM SYSIBM.SYSVIEWDEP H ,PARENTS P WHERE H.BNAME = P.VVIEW AND P.LEVEL > -20 ) SELECT BCREATOR, VVIEW, LEVEL FROM ADK.CHILDREN UNION ALL SELECT BCREATOR, VVIEW, LEVEL FROM PARENTS WITH UR ;

COMMIT ;

==============================================================

-- RESULTS:


BCREATOR VVIEW LEVEL

---------- ----------------------------

SYSIBM PARAMETERS_S -1

SYSIBM SYSTRANSFORMS 1

SYSIBM SYSROUTINES 1

SYSIBM SYSROUTINEPARMS 1

4 record(s) selected.


Commit complete.

==============================================================

The views expressed are my own and not necessarily those of Database-Brothers (DBI) and its affiliates.

The views and opinions expressed by visitors for this here blog are theirs and do not necessarily reflect mine .

I've been using databases for far too long; working way too hard and spending too little time on golf and spending far too much time working on computers since before invention of ...

I welcome any comments to Alexander.Kopac@database-brothers.com

==============================================================

Hello Ya'll: (All Ya'll is plural in Austin, Texas)

As some of you may know, my official title at DBI' for the past several years has been "Database Magician"; (more likely because we all wear many interchangable hats than anything else).

I've also been known to speak on 'Parlez-Vous Klingon? Recursion SQL for Database Magicians - 'Generating Test Data' for Oracle databases.

I've also been known on many ocassions to speak on 'Parlez-Vous Klingon? Recursion SQL for Generating Test Data' for IBM DB2 databases. There will posts for DB2 too!

Therefore, what better way to start my first ever Blog entry by offering some 'magic' - this time its for all ya'll Oracle database practitioners.

Earlier this week, I was fortunate enough to be invited for free pizza (DBA geek food) and to speak at the Austin (Texas) Oracle Users Group where I spoke about 'Database Auditing for Compliance, Security,& Reputation Management'.

During the session, I promised the 57 attendees that I would make some Oracle SQL available for Jumbling/Obfucating/Scrambling Production Data for Testing environment purposes.

(BTW, Watch out Vancouver, BC, I'll be at BMC Userworld on Nov 2 and giving up the same stuff

Well, what follows is perhaps some Jumbalaya magic - Jumbling/Obsfucating Production Data for Testing Purposes !

As many of you know, there are many application vendors of database "Data Quality & Cleansing tools" that prepare/check for valid addresses for USPS handling by verifying, standardizing, and correcting address elements, as well as producing delivery point barcodes in order to enable pre-sorting for postal automation discounts.

Many times, the technique that I am providing below can be used to help get thru these 'testing stangleholds' or bottlenecks and mask the real data owners.

Of course, ya'll must try this all out with sqlplus scott/tiger @jumbalaya.sql > jumbalaya.txt /NOLOG

The sample below (jumbalaya.sql) takes the address from the next-row after the row read (in-hand) and substitutes/updates/puts it into the current row thereby "cornfusing; murdalating and obsfucat'n" the darn data.

The point here is that the address is/remains "valid" in order to test pass thru various vendor tools that perform 'full-featured address validation' for system testing in non-production environments; (but perhaps good enough to possibly begin thwarting data theft).

(After applying my technique below, consider taking it many steps further; grab first_name_txt or last_name_txt, etc. and change the function below and do the same jumbalingling thing for many more columns of data. I'll do future blog entries for doing that too

Here's how it works: (For some background info : Google: over "analytic functions" oracle nvl )

a) Look below (jumbalaya.sql) file/data and look for a column named address_txt .
(jumbalaya.sql) creates a table, inserts rows, selects rows, performs the jumbalaya function, and selects rows results.

b) Next, look for (jumbalaya.txt) file/data below which contains the results.
It uses the nvl & over "analytic functions" of Oracle.

First, notice that CLIENT_NBR goes from 1-15 rows when the street names are inserted; they start with first thru fifteenth streets respectively.

After the Oracle PL/SQL function is run, notice that the CLIENT_NBR is one-off/jumbled from the street names.

Don't forget, " Ya'll test drive the jumbalaya.sql SQL yourself ! " ; that's Ya'll is singular in Texas; All Ya'll is plural.

Ya'll lemme know if I have described it properly and if you find this technique useful !

Alexander.Kopac@Database-Brothers.com

===============================================

BTW, here's the short form of the Oracle magic:

-- Step 03 - "Jumbling" the address_txt column data
declare
-- Grab and hold the rows for the Update Jumbling
cursor grab1cursor is select client_nbr,
nvl ( lead (address_txt) over (order by client_nbr),
first_value(address_txt) over (order by client_nbr)) next_address_txt
from client_data
for update ;
begin
for update1loop in grab1cursor loop
update client_data
set address_txt = update1loop.next_address_txt
where current of grab1cursor ;
EXIT WHEN grab1cursor%NOTFOUND ;
end loop ;
commit ;
end ;
/

==========================================

Here's the long form for the Oracle magic:

jumbalaya.sql:

-- jumbalaya.sql

-- -- JUMBLING PRODUCTION DATA.TXT

-- sqlplus alex/alex @jumbalaya.sql > jumbalaya.txt /NOLOG

-- =============================================================================

-- JUMBLING PRODUCTION DATA - Table definitions:

-- 12345678901234567890123456789012345678901234567890

--=============================================================================

-- Step 01 - Kill/Create the table to be "Jumbled"

drop table client_data ;

COMMIT ;

CREATE TABLE client_data
(
client_nbr NUMBER(10) not null,
client_type_txt VARCHAR2(10),
first_name_txt VARCHAR2(30),
last_name_txt VARCHAR2(30),
client_region_txt VARCHAR2(5),
credit_limit_amt NUMBER(10,2),
address_txt VARCHAR2(50),
CONSTRAINT client_pk PRIMARY KEY (client_nbr)
) ;

COMMIT ;

describe client_data ;

COMMIT ;

GRANT SELECT, INSERT, UPDATE, DELETE ON client_data to public ;

COMMIT ;

select count(*) from client_data ;

-- Step 02 - INSERT Production data in the client_data table to be "Jumbled"

INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt , last_name_txt , client_region_txt, credit_limit_amt, address_txt )
VALUES ( 1, 'SILVER', 'Harry', 'Hill', 'NORTH', 11000.00, '11111 First Avenue Austin, TX 11111-1234' ) ;
COMMIT ;

INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES ( 2, 'SILVER', 'Vic', 'Reeves', 'NORTH', 2000.00, '20202 Second Avenue Austin, TX 20202-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES ( 3, 'SILVER', 'Bob', 'Mortimer', 'WEST', 500.00, '30303 Third Avenue Austin, TX 30303-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES ( 4, 'SILVER', 'Paul', 'Whitehouse', 'SOUTH', 1000.00, '40404 Fourth Avenue Austin, TX 40404-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES ( 5, 'SILVER', 'Harry', 'Enfield', 'EAST', 20000.00, '50505 Fifth Avenue Austin, TX 50505-1234' ) ;

INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES ( 6, 'GOLD', 'Jenifer', 'Lopez', 'WEST', 500.00, '60606 Sixth Avenue Austin, TX 60606-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES ( 7, 'GOLD', 'Kylie', 'Minogue', 'NORTH', 1000.00, '70707 Seventh Avenue Austin, TX 70707-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES ( 8, 'GOLD', 'Maria', 'Carey', 'WEST', 1000.00, '80808 Eighth Avenue Austin, TX 80808-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES ( 9, 'GOLD', 'Dani', 'Minogue', 'SOUTH', 20000.00, '90909 Nineth Avenue Austin, TX 90909-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES (10, 'GOLD', 'Whitney', 'Houston', 'EAST', 500.00, '1010 Tenth Avenue Austin, TX 10101-1234' ) ;

INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES (11, 'PLATINUM', 'Robbie', 'Williams', 'SOUTH', 500.00, '1111 Eleventh Avenue Austin, TX 11111-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES (12, 'PLATINUM', 'Thom', 'Yorke', 'NORTH', 2000.00, '1212 Twelveth Avenue Austin, TX 12121-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES (13, 'PLATINUM', 'Gareth', 'Gates', 'WEST', 10000.00, '1313 Thirteenth Avenue Austin, TX 13131-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES (14, 'PLATINUM', 'Darius', 'Dinesh', 'EAST', 2000.00, '1414 Fourteenth Avenue Austin, TX 14141-1234' ) ;
INSERT INTO client_data (client_nbr, client_type_txt, first_name_txt, last_name_txt, client_region_txt, credit_limit_amt, address_txt )
VALUES (15, 'PLATINUM', 'Will', 'Young', 'EAST', 100.00, '1515 Fifteenth Avenue Austin, TX 15151-1234' ) ;

COMMIT ;

select count(*) from client_data ;

select client_nbr, last_name_txt, address_txt from client_data order by client_nbr ;

COMMIT ;

-- Step 03 - "Jumbling" the address_txt column data
declare
-- Grab and hold the rows for the Update Jumbling
cursor grab1cursor is select client_nbr,
nvl ( lead (address_txt) over (order by client_nbr),
first_value(address_txt) over (order by client_nbr)) next_address_txt
from client_data
for update ;
begin
for update1loop in grab1cursor loop
update client_data
set address_txt = update1loop.next_address_txt
where current of grab1cursor ;
EXIT WHEN grab1cursor%NOTFOUND ;
end loop ;
commit ;
end ;
/

-- Step 04 - Review the results of "Jumbling" the address_txt column data


select count(*) from client_data ;

select client_nbr, last_name_txt, address_txt from client_data order by client_nbr ;

COMMIT ;

exit ;

==========================================

HERE'S THE RESULTS jumbalaya.txtl:


SQL*Plus: Release 10.2.0.2.0 - Production on Thu Oct 18 16:19:12 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options


Table dropped.


Commit complete.


Table created.


Commit complete.

Name Null? Type
----------------------------------------- -------- ----------------------------
CLIENT_NBR NOT NULL NUMBER(10)
CLIENT_TYPE_TXT VARCHAR2(10)
FIRST_NAME_TXT VARCHAR2(30)
LAST_NAME_TXT VARCHAR2(30)
CLIENT_REGION_TXT VARCHAR2(5)
CREDIT_LIMIT_AMT NUMBER(10,2)
ADDRESS_TXT VARCHAR2(50)


Commit complete.


Grant succeeded.


Commit complete.


COUNT(*)
----------
0


1 row created.


Commit complete.


1 row created.


. . .


1 row created.


Commit complete.


COUNT(*)
----------
15


CLIENT_NBR LAST_NAME_TXT
---------- ------------------------------
ADDRESS_TXT
--------------------------------------------------
1 Hill
11111 First Avenue Austin, TX 11111-1234

2 Reeves
20202 Second Avenue Austin, TX 20202-1234

3 Mortimer
30303 Third Avenue Austin, TX 30303-1234


CLIENT_NBR LAST_NAME_TXT
---------- ------------------------------
ADDRESS_TXT
--------------------------------------------------
4 Whitehouse
40404 Fourth Avenue Austin, TX 40404-1234

5 Enfield
50505 Fifth Avenue Austin, TX 50505-1234

6 Lopez
60606 Sixth Avenue Austin, TX 60606-1234


CLIENT_NBR LAST_NAME_TXT
---------- ------------------------------
ADDRESS_TXT
--------------------------------------------------
7 Minogue
70707 Seventh Avenue Austin, TX 70707-1234

8 Carey
80808 Eighth Avenue Austin, TX 80808-1234

9 Minogue
90909 Nineth Avenue Austin, TX 90909-1234


CLIENT_NBR LAST_NAME_TXT
---------- ------------------------------
ADDRESS_TXT
--------------------------------------------------
10 Houston
1010 Tenth Avenue Austin, TX 10101-1234

11 Williams
1111 Eleventh Avenue Austin, TX 11111-1234

12 Yorke
1212 Twelveth Avenue Austin, TX 12121-1234


CLIENT_NBR LAST_NAME_TXT
---------- ------------------------------
ADDRESS_TXT
--------------------------------------------------
13 Gates
1313 Thirteenth Avenue Austin, TX 13131-1234

14 Dinesh
1414 Fourteenth Avenue Austin, TX 14141-1234

15 Young
1515 Fifteenth Avenue Austin, TX 15151-1234


15 rows selected.


Commit complete.


PL/SQL procedure successfully completed.


COUNT(*)
----------
15


CLIENT_NBR LAST_NAME_TXT
---------- ------------------------------
ADDRESS_TXT
--------------------------------------------------
1 Hill
20202 Second Avenue Austin, TX 20202-1234

2 Reeves
30303 Third Avenue Austin, TX 30303-1234

3 Mortimer
40404 Fourth Avenue Austin, TX 40404-1234


CLIENT_NBR LAST_NAME_TXT
---------- ------------------------------
ADDRESS_TXT
--------------------------------------------------
4 Whitehouse
50505 Fifth Avenue Austin, TX 50505-1234

5 Enfield
60606 Sixth Avenue Austin, TX 60606-1234

6 Lopez
70707 Seventh Avenue Austin, TX 70707-1234


CLIENT_NBR LAST_NAME_TXT
---------- ------------------------------
ADDRESS_TXT
--------------------------------------------------
7 Minogue
80808 Eighth Avenue Austin, TX 80808-1234

8 Carey
90909 Nineth Avenue Austin, TX 90909-1234

9 Minogue
1010 Tenth Avenue Austin, TX 10101-1234


CLIENT_NBR LAST_NAME_TXT
---------- ------------------------------
ADDRESS_TXT
--------------------------------------------------
10 Houston
1111 Eleventh Avenue Austin, TX 11111-1234

11 Williams
1212 Twelveth Avenue Austin, TX 12121-1234

12 Yorke
1313 Thirteenth Avenue Austin, TX 13131-1234


CLIENT_NBR LAST_NAME_TXT
---------- ------------------------------
ADDRESS_TXT
--------------------------------------------------
13 Gates
1414 Fourteenth Avenue Austin, TX 14141-1234

14 Dinesh
1515 Fifteenth Avenue Austin, TX 15151-1234

15 Young
11111 First Avenue Austin, TX 11111-1234

15 rows selected.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

=============================================

The views expressed are my own and not necessarily those of Database-Brothers (DBI) and its affiliates.

The views and opinions expressed by visitors to this here blog are theirs and do not necessarily reflect mine.

I've been using databases for far too long; working way too hard and spending too little time on golf and spending far too much time working on computers since before invention of ...

I welcome any comments to Alexander.Kopac@database-brothers.com