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