NOTE: The Amateur Data Base on the internet is still in a testing stage. We are making every attempt to get it right the first time, but there may be glitches. For this we apologize. Please send any questions, comments, complaints to mkemper@fcc.gov One immediate item we are exploring is how to break up the weekly zip file. Let us know your opinion. Thanks for your patience. The Amateur data base as found on the Internet consists of two portions, The entire Amateur data base (currently delivered as one file for each table in the data base) that is refreshed each week by noon Monday and daily transaction files. The transaction files are named for the day of the week that the transactions take place (mon.zip, tue.zip, wed.zip. etc.) and are put on the internet nightly. Saturday has been included to fully automate the data transference, although it the sat.zip file will be mostly empty. The transaction files will remain for a week, at which time they will be overlayed. ALL ZIPPED FILES MAINTAIN THIER ORIGINAL NAME, and today's files will overlay yesterday's files! Approximate file sizes are: Zipped Unzipped Full data base 32 Mbytes 144 Mbytes Transactions 4-10 Kbytes 6-50 Kbytes All files are zipped and each line of data ends in a carriage return/line feed. It has been reported that downloading the full data base took 7 hours using a 14.4 Mbyte modem and cost under $20 using Prodigy. The six files of the Amateur data base are: TABLE DESCRIPTION FILE l_appl application appl.dat l_indiv individual indiv.dat l_alien alien alien.dat l_crm clubs, races, military crm.dat l_admin administrative notes admin.dat l_codes callsigns to be removed codes.dat The file layouts (in the form of SYBASE table CREATE statements are: CREATE TABLE extract_indiv (id_callsign varchar(10), id_fee_data varchar(32) NULL, callsign_group char(1) NULL, callsign_prev varchar(10) NULL, type_op_class char(1), flag_nepa char(1), type_prev_op_class char(1) NULL) CREATE TABLE extract_appl (id_callsign varchar(10), id_file_num char(10), type_purpose varchar(8), type_applicant char(1), name_licensee varchar(40), text_street varchar(35) NULL, text_pobox varchar(20) NULL, city varchar(20), state char(2), zipcode varchar(9), date_of_birth char(6) NULL, date_issue char(6), date_expire char(6), date_last_change char(6), id_examiner char(3), flag_renewal_notice char(1) NULL, date_renewal_notice char(6) NULL) CREATE TABLE extract_alien (id_callsign varchar(10), country_alien varchar(25), text_recip_address1 varchar(40) NULL, text_recip_address2 varchar(40) NULL, text_recip_address3 varchar(40) NULL, text_recip_address4 varchar(40) NULL, text_alien_address1 varchar(40) NULL, text_alien_address2 varchar(40) NULL, text_alien_address3 varchar(40) NULL, text_alien_address4 varchar(40) NULL, text_attn_line varchar(35) NULL) CREATE TABLE extract_crm (id_callsign varchar(10), id_fee_data varchar(32) NULL, name_entity varchar(50), text_attn_line varchar(35) NULL, callsign_trustee varchar(10) NULL, flag_nepa char(1)) CREATE TABLE extract_admin (id_callsign varchar(10), text_spec_cond varchar(100) NULL, text_admin_note varchar(100) NULL) CREATE TABLE extract_codes (id_callsign varchar(10), type_delete_cancel char(1) NULL, date_cancel char(6) NULL) l_appl is the driving table, and any change in it may be reflected in one or more of the other tables. The link is id_callsign. Date fields for the daily extracts are in the format yymmdd. For the full data base, they are in SYBASE datetime format (Jan 01, 1995 12:00:00AM). Selected definitions: callsign_group - Defines a format, size, and prefix priority of call signs and is used to assign choice call signs to licensees with higher operator classes. flag_nepa - Indicates that proposed application will meet or not meet Section 1.1305 of the FCC rules regarding environmental impact (from form 610). id_fee_data - Two 16 position fields indicating deposit date, associated lock box, and batch. This field is for filing fee data which may be added to the record at a later time. type_applicant - Indicates type of application. Codes are: A Alien C Club I Individual M Military recreation R RACES type_delete_cancel - Provides selection of selected actions taken against applicant/licensee record. For example, identifies reason for cancellation, revocation, and voluntary withdrawal. Codes are: C Cancel R Revoke V Voluntary Withdrawal P Purge O Other A Deletion caused by other type transaction (replacement) type_op_class - Indicates the class of operator license presently held by the applicant. Codes are: N Novice T Technician P Technician Plus G General A Advanced E Extra type_purpose - Indicates the reason why the application was filed. Multi- ple codes can occur. Codes are: A New license B Change existing class C Change name D Change mailing address E Change callsign F Renewal on form 610 G Add record (internal) H Duplicate license request I Change Issue/Expiration Date J Supercede K Internal correction code L Delete N Renewal on form 610R O Renewal on form 610B P Modification on form 610B Q Restore both database and pending R Restore database S Special callsign change