Federal Communication Commission (FCC) Special Access Technical Manual Special Access Technical Manual v1.0 1 Special Access Database Container Technical Manual Date: December 9, 2014 Version 1.0 Special Access Technical Manual v1.0 2 TABLE OF CONTENTS TABLE OF CONTENTS.................................................................................................. 2 1 INTRODUCTION ...................................................................................................... 3 1.1 DOCUMENT AND PROCESS OVERVIEW ........................................................................ 3 2 GETTING STARTED................................................................................................ 6 2.1 GETTING STARTED SUMMARY .................................................................................... 6 2.2 JAVA – IDENTIFYING WHAT VERSION OF JAVA YOUR SYSTEM USES ........................... 7 2.3 32-BIT OR 64-BIT VERSION OF WINDOWS................................................................... 9 2.4 ORACLE ................................................................................................................. 10 3 ORACLE EXPRESS AND DATABASE CONTAINER SET UP PROCESS .......... 11 3.1 COPY TEMPLATES AND SQL SCRIPTS TO YOUR C: DRIVE .......................................... 11 3.2 INSTALL ORACLE EXPRESS (WINDOWS PC).............................................................. 16 3.3 CREATE THE DATABASE CONTAINER ........................................................................ 23 4 USING THE DATABASE CONTAINER ................................................................. 30 4.1 LOGGING INTO THE DATABASE.................................................................................. 30 4.2 CONVERTING .CSV FILES TO .TXT FILES .................................................................... 30 4.3 LAUNCHING THE GRAPHICAL USER INTERFACE (GUI) ................................................ 31 4.4 LOADING ERRORS................................................................................................... 40 5 REFERENCE INFORMATION ............................................................................... 45 5.1 GUI FILE NAMES AND COLLECTION QUESTIONS ........................................................ 45 5.2 TABLE SPECIFICATION FORMAT FOR THE SPADC_FILER_ID_INFO.TXT FILE............ 48 5.3 OPTIONAL FIELDS ................................................................................................... 49 5.4 FIELD CHARACTER LIMITS ....................................................................................... 55 6 TROUBLESHOOTING ........................................................................................... 57 6.1 COMPILATION ERRORS ............................................................................................ 57 6.2 USER IDENTIFICATION .............................................................................................. 58 Special Access Technical Manual v1.0 3 Tip Having trouble? 1 INTRODUCTION The FCC is conducting a one-time, mandatory collection of data and information from providers and purchasers of special access services in areas where the incumbent is subject to price cap regulation (i.e., price cap areas) and certain entities that provide best efforts business broadband Internet access service in price cap areas. The goal of the collection is obtain data and information for a comprehensive analysis of the special access market to aid the Commission in evaluating whether changes to the pricing flexibility rules are needed to ensure that rates, terms and conditions are just and reasonable. The FCC has launched a secure web portal for the electronic filing of responses to the data collection. This document serves as a guide for setting up and loading the database container in accordance with the table format specifications outlined in the “Instructions for the Data Collection,” http://transition.fcc.gov/wcb/Instructions091814.pdf. For additional information on about special access and the data collection, please refer to the FCC’s Special Access Data Collection Overview webpage, http://www.fcc.gov/encyclopedia/special-access-data-collection-overview-0. 1.1 DOCUMENT AND PROCESS OVERVIEW This document is organized into the following six sections: 1. Introduction 2. Getting Started (System Requirements) 3. Oracle Express and Database Container Set Up Process 4. Using the Database 5. Reference Information 6. Troubleshooting Follow the instructions in sections 2-4 for a successful data submission to the FCC. The steps must be completed in sequential order. Do not proceed until you have completed all the steps in a section. Sections 5 and 6 provide additional reference and troubleshooting information. Throughout this manual, you will see “Having Trouble?” boxes displayed in red, such as the one to the right. These boxes have trouble shooting instructions. You will also see “Tip” boxes displayed in green, providing helpful hints to make the Special Access data collection a smooth process. Special Access Technical Manual v1.0 4 The subsections below provide a general overview of the steps involved in preparing your information for delivery to the FCC. For detailed instructions, please continue to section 3. 2 Getting Started ? Identify the system requirements 3 Oracle Express and Database Container Set Up Process Overview - Copying Files to your Computer and Creating the Database Container Shell All information must be delivered to the FCC in a database container specifically designed for Special Access data. There are three primary steps to downloading the database container: ? Copy the Templates and SQL scripts included in the email sent to you to your C: drive. o These files include the information that will be used to create the database “container.” o See the Copy Templates and SQL Scripts to Your C: Drive section for instructions. ? Install Oracle Express on your Windows PC. o Oracle Express is used to create and manage databases locally on your computer. The use of Oracle Express is required to successfully create and fill the database container. o See the Install Oracle Express (Windows PC) section for instructions. ? Create the database “container” on your computer o You will give a series of commands to Oracle Express to read the files on your C: drive to create the database on your computer. o See the Create the Database Container section for detailed instructions. o Accessing the Oracle Express Database after you have created your database container 4 Using the Database Container Your data must be properly formatted and prepared before it can be sent to the FCC. The steps below outline the general process you must follow for your data to be successfully delivered to the FCC. ? Data Preparation o .txt files organized according to the table format specifications contained in the “Instructions for the Data Collection,” http://transition.fcc.gov/wcb/Instructions091814.pdf. o See the GUI File Names and Collection Questions section and the provided table creation instructions for details ? Launching the Graphical User Interface (GUI) o The GUI is used to load your database container with .txt files from your computer network and create the DMP file that will be zipped and uploaded through the FCC’s secure web portal for electronic submission. ? Loading your data into the database container o Your data must be successfully loaded into the container and pass a quality control check before it can be submitted to the FCC. Special Access Technical Manual v1.0 5 5 Reference Information This information will be helpful for troubleshooting and questions that might arise throughout the process: ? GUI File names and Collection Questions ? Table Specification Format for the SPADC_FILER_ID_INFO.txt File ? Optional Fields ? Field Character Limits 6 Troubleshooting ? Compilation Errors ? User Identification Special Access Technical Manual v1.0 6 2 GETTING STARTED This manual is written for use with Windows PC computers. Before going through the steps outlined in this manual, you should first verify that your computer meets the following system requirements: 2.1 GETTING STARTED SUMMARY The system requirements are: 2.1.1 Your computer uses Java 6 or higher Instructions on how to identify what version of JAVA your system is using can be found in Section 2.2. 2.1.2 Your Computer Uses a 32-Bit or 64-Bit operating system Instructions on how to determine what version of windows your system uses can be found at in Section 2.3. 2.1.3 You have Administrator Rights If you are using a computer that is on a company network, you must have Administrator Rights to download and install Oracle Express on your computer. 2.1.4 Your Computer does not have Oracle Express Please verify that no version of Oracle Express is already installed on your computer. See Section 2.4 for more details 2.1.5 Your firewall does not block specific Files The computer’s firewall does not block.bat, apache, junit, or oracle jdbc file types. Special Access Technical Manual v1.0 7 2.2 JAVA – IDENTIFYING WHAT VERSION OF JAVA YOUR SYSTEM USES Identifying what version of Java is used by your system varies slightly depending upon your system configuration. The following instructions are for the most common system configurations. These instructions will take you to the “Java Control Panel” where you will be able to click on the “About” tab to see the Java version your system uses. (See Figure 1 below) Special Access Technical Manual v1.0 8 2.2.1 For Windows 8 2.2.1.1 Select the Windows logo key and the W key simultaneously to open the Search charm to search settings. OR Drag the mouse pointer to the bottom-right corner of the screen and then click on the Search icon. 2.2.1.2 In the search box enter “Java Control Panel.” 2.2.1.3 Click on Java icon to open the Java Control Panel. 2.2.1.4 Click on the “About” tab. 2.2.2 For Windows 7 or Vista 2.2.2.1 Click on the Start button at the lower left hand corner of the computer’s desktop and then click on the Control Panel option. 2.2.2.2 In the Control Panel Search enter Java Control Panel 2.2.2.3 Click on the Java icon to open the Java Control Panel 2.2.2.4 Click on the “About” tab 2.2.3 For Windows XP 2.2.3.1 Click on the Start button at the lower left hand corner of the computer’s desktop and then click on the Control Panel option. 2.2.3.2 Double click on the Java icon to open the Java Control Panel. 2.2.3.3 Click on the “About’ button Figure 1 – JAVA Control Panel Special Access Technical Manual v1.0 9 2.3 32-BIT OR 64-BIT VERSION OF WINDOWS Oracle Express has different versions to install for 32-Bit and 64-Bit operating systems. Identifying whether your computer uses the 32-Bit or 64-Bit version of windows varies slightly depending upon your system configuration. The following instructions are for the most common system configurations. 2.3.1 For Windows 8 2.3.1.1 While on the Start screen, swipe up to switch to the Apps screen. OR With a mouse, click on the downward-facing arrow icon to bring up the Apps screen. Note: Prior to the Windows 8.1 update, the Apps screen is accessible by swiping up from the bottom of the screen, or right-clicking anywhere, and then choosing All Apps. 2.3.1.2 On the Apps screen, swipe or scroll to the right and find the Windows System category. 2.3.1.3 Press or click on the Control Panel icon under Windows System. 2.3.1.4 Windows 8 will switch to the Desktop and open the Control Panel. By default, the Control Panel in Windows 8 opens in the Category view, but I recommend changing it to the arguably easier to manage Small icons or Large icons view. 2.3.1.5 Touch or click on System and Security within Control Panel. Note: You won't see a System and Security link in Control Panel if your view is set to either Large icons or Small icons. If so, find System and touch or click on it, then skip to Step 4. 2.3.1.6 With the System and Security window now open, click or touch System. 2.3.1.7 With the System applet now open, titled View basic information about your computer, find the System area, located under the large Windows 8 logo. The System type will say either 32-Bit Operating System or 64-Bit Operating System. 2.3.2 For Windows 7 or Vista 2.3.2.1 Open System by clicking the Start button in the lower left hand corner of the computer’s desktop. 2.3.2.2 Right-click Computer. 2.3.2.3 Select Properties. 2.3.2.4 Under System, you can view the system type. 2.3.3 For Windows XP 2.3.3.1 Click Start. 2.3.3.2 Right-click My Computer and then click Properties. 2.3.3.3 If you don't see "x64 Edition" listed, then you're running the 32-Bit version of Windows XP. 2.3.3.4 If "x64 Edition" is listed under System, you're running the 64-Bit version of Windows XP. Special Access Technical Manual v1.0 10 2.4 ORACLE If you have previously downloaded Oracle Express 11g or higher, follow the below instructions. 2.4.1 Make sure you have admin rights in Oracle (you need to be able to grant rights to other users i.e. schemas) 2.4.2 Continue to Step 3.1 and copy the Templates and SQL scripts to your C drive 2.4.3 You can then skip the Oracle installation process (Section 3.2) and proceed to Step 3.3.2.3 to set up the spadc user and continue with the instructions If you have previously downloaded a version of Oracle Express that is lower than 11g, please uninstall the program. You will then reinstall and follow the instructions beginning at Section 3 If you have previously downloaded Oracle, follow the below instructions. 2.4.4 Make sure you have admin rights in Oracle (you need to be able to grant rights to other users i.e. schemas) 2.4.5 Continue to Step 3.1 and copy the Templates and SQL scripts to your C drive 2.4.6 You can then skip the Oracle installation process (Section 3.2) and proceed to Step 3.3.2.3 to set up the spadc user and continue with the instructions Special Access Technical Manual v1.0 11 3 ORACLE EXPRESS AND DATABASE CONTAINER SET UP PROCESS This section contains the instructions to the three steps to download the database container. These include: 3.1 Copy Templates and SQL Scripts to Your C: Drive 3.2 Install Oracle Express (Windows PC) 3.3 Create the Database Container Once Oracle Express is installed and the database container is created, you will not need to repeat the following steps. 3.1 COPY TEMPLATES AND SQL SCRIPTS TO YOUR C: DRIVE 3.1.1 Download Database Container .zip file 3.1.1.1 After logging into the secure web portal, https://specialaccessfiling.fcc.gov/spadc/login, you will be directed to the “Getting Started” page. 3.1.1.2 On this page, click on the Database Container Download link. Figure 2 – Special Access data Collection Getting Started Page This will direct you to another webpage, http://www.fcc.gov/encyclopedia/database- container. Special Access Technical Manual v1.0 12 Figure 3 – Database Container Page of FCC Encyclopedia Note: You can access the Database Container page without having to log in to the secure web portal by just directing your web browser to the http://www.fcc.gov/encyclopedia/database-container page. Special Access Technical Manual v1.0 13 3.1.2 The Database Container Download Page: 3.1.2.1 This page contains links for the Technical Manual, a .zip file containing the scripts used to create and validate the database container, and a .zip containing sample .txt files to test loading the container. 3.1.2.2 Select the link to download and open the Database Container file, as shown below. Figure 4 – Opening the Zip Files Special Access Technical Manual v1.0 14 3.1.2.3 Open the .zip file by double clicking the Database_Container_V#.# folder. You will need to save the SPADC folder onto your computer (i.e., copy and paste the folder). Figure 5 – Saving the Database Container Zip File to Your Computer Below are two sets of instructions for saving and opening the downloaded Database Container zip file: 3.1.3 for using a c:\drive (highly recommended) and 3.1.4 if you do NOT have a c:\ drive and instead use another network drive, e.g., an N:\ drive. You can follow these same instructions for opening and saving the folder containing the Sample Data files to your computer. Special Access Technical Manual v1.0 15 3.1.3 If you have a c:\ drive, follow the below instructions: 3.1.3.1 Open the zipped Database Container file and copy the folder named “SPADC.” 3.1.3.2 Go to your c:\ drive and paste the copied folder. 3.1.3.3 The folder you are saving is named SPADC. Therefore, if you have an existing folder on your c:\ drive named SPADC, rename the existing folder. 3.1.3.4 Ensure the files are in the proper formats (.txt NOT .xlsx). 3.1.4 If you do not have a C:\drive: 3.1.4.1 Save the files in the available drive, such as the N:\ or D:\ drive. 3.1.4.2 You will then need to change all references to the C:\ drive in the steps throughout these instructions to the drive you selected. Special Access Technical Manual v1.0 16 3.2 INSTALL ORACLE EXPRESS (WINDOWS PC) Oracle Express is a free download program that provides users with an interface to create and administer databases and run SQL command scripts. You will need Oracle Express to set up the database container and load your .txt files into the database container. Please note that if you are loading more than 11 GB of data into the database container, you will not be able to use Oracle Express and will instead need to obtain the licensed version of Oracle, which is not free. Oracle Express has different versions for 32-bit and 64-bit Windows systems. You must install the correct Oracle Express version for your system. The screen shots shown in this section are for a 64-bit Windows operating system but the same steps apply when installing the 32-Bit version of Oracle Express. 3.2.1 Open a web browser e.g. Mozilla Firefox, Internet Explorer, or Google Chrome. 3.2.2 Navigate to http://www.oracle.com/technetwork/database/database-technologies/express- edition/downloads/index.html You may do this by clicking on the URL; cutting and pasting the URL into your web browser; navigating through the Oracle website; or using a search function. 3.2.3 Click the “Accept License Agreement” button indicated below NOTE: Oracle Express WILL work on Windows x64. After you click the “Accept License Agreement” button, it will disappear. Figure 6 – Oracle Express Download Screen (License Agreement) Special Access Technical Manual v1.0 17 3.2.4 If your computer is running the 64-Bit version of Windows, click the link indicated in the below screenshot. If your computer is using the 32-Bit version of Windows, select the link labeled “Oracle Database Express Edition 11g Release 2 for Windows x32.” Figure 7 – Oracle Express Download Screen 3.2.5 Creating an Oracle Account: To download Oracle Express, you will need to sign up for a free Oracle account. To create an account, click “Create Account” and follow the instructions on the Oracle website. Figure 8 – Creating Oracle Account Special Access Technical Manual v1.0 18 Tip You may use the SAME password for both accounts. Be sure to remember both passwords. The instructions for setting up an Oracle account are also listed below: 3.2.5.1 Creating the Account Password Please note the passwords must only contain alphanumeric characters (i.e., only numbers and letters, no special characters like @!% or spaces). When downloading and installing Oracle Express, you will be asked to create two passwords: in this step, you will create an account with Oracle (Account Password) and in step 3.2.6.4, you will create the password needed to access your work in Oracle Express (System Password). 3.2.5.2 Once the account is created, you will receive this message that will ask you to validate your email address. Figure 9 – Oracle Account Creation Confirmation 3.2.5.3 To validate your email address, you will need to check the email address account you provided Oracle when setting up the account. You should receive an email similar to the email message example below. Figure 10 – Oracle Account Email Verification Special Access Technical Manual v1.0 19 3.2.5.4 Verify your email by clicking on the “Verify Email Address” link within the email. You will then receive the following message: Figure 11 – Oracle Account Email Verified 3.2.5.5 Navigate to the web browser page where you received the successful account created message and select continue (see below). Figure 12 – Oracle Account Creation Verification 3.2.6 Saving and Installing Oracle Express 3.2.6.1 Once you select “Continue,” you will receive the following message. Click the “Save” button to download the compressed folder. Figure 13 – Saving Oracle Express Special Access Technical Manual v1.0 20 After selecting the “Save” button, a progress bar will appear, as shown below: Figure 14 – Saving Oracle Express Progress Verify Oracle Express has been downloaded onto your C: drive in a folder labeled “oraclexe.” It should automatically save to this location. If Oracle does not save to your C: drive, please move it to the C: drive. 3.2.6.2 Once the file is saved to your computer, use Windows Explorer to navigate to the extracted content and open the “DISK1” folder. Note: You may save Oracle Express in any folder your chose however you must know where you are saving it in order to access this folder in the next step to begin installing Oracle Express onto your computer. 3.2.6.3 Inside the folder “DISK1” will be a “setup.exe” file. Double click it to begin the installation process and follow the step-by-step instructions provided in the InstallShield Wizard to install Oracle Express. Figure 15 – Oracle Express File Location Special Access Technical Manual v1.0 21 Tip Please note the System Password may be the same as the Account Password. Figure 16 – Oracle Express InstallShield Wizard Below are screenshots of the InstallShield Wizard process. 3.2.6.4 Creating the System Password During this process you will be asked to create the second password, also referred to as the “System Password.” You will use the System Password as you follow the prompts to load Oracle Express as detailed in the screenshots in this step. The password must only contain alphanumeric characters (i.e., only numbers and letters, no special characters like @!% or spaces). 1 2 Special Access Technical Manual v1.0 22 3 4 5 6 7 Special Access Technical Manual v1.0 23 Tip If you are using the C Command Prompt, you can “paste” copied text in the Command Prompt window. To paste text, place cursor in location you want to paste text then, right click and choose paste option from pop-up window list. Please note that Ctrl + v pasting will NOT work in C Command Prompts. 3.3 CREATE THE DATABASE CONTAINER The database container is the database shell structure that will store the data organized in the table specification format as set forth in the Data Collection Instructions. Throughout this section of the instructions, you will be required to enter command codes using the Oracle Express program. The commands are highlighted in yellow. If you are using a SQL Command Prompt, which is recommended, please note that you will not be able to copy and paste the code provided in this manual. Therefore you must type the code exactly as it is written into the SQL Command Prompt as specified below. You are required to do the following steps to create the database shell. After the database shell is created, you will not need to repeat these steps. 3.3.1 Once Oracle Express is installed, open a SQL Command Prompt window using the following steps: 3.3.1.1 Click the Start button, which is the icon located at the bottom left corner of your desktop: Figure 17 – All Programs 3.3.1.2 Click All Programs 3.3.1.3 Navigate to and click on the “Oracle Database 11g Express Edition” folder 3.3.1.4 Select “Run SQL Command Line” A SQL Command Prompt will appear. Please see the figure below for an example of the SQL Command Prompt: You are logged on as “system” Special Access Technical Manual v1.0 24 Having Trouble? Please note that file and path Names are CASE SENSITIVE. Figure 18 – SQL Command Line 3.3.2 You will now log into the database as the “system” user. 3.3.2.1 The user ID and password you will use for this section are noted below: ID = system Password = The System Password you created in Step 3.2.6.4 3.3.2.2 To log into the database as the system user from the SQL Command Prompt, enter the highlighted text below into the SQL Command Prompt and press the Enter key: SQL> connect Enter user-name: system Enter password: [The System password you created in step 3.2.6.4] Note: the password will not appear in the SQL Command Prompt as you type it. This is a security feature to prevent another person from viewing your password. When you enter your system password, press the Enter key and the system will log you into the database. A successful log in attempt is shown in the below figure. Figure 19 – Logged into the Database as the System User If you have trouble identifying which user you are currently logged in as, see section 6.2 for guidance. You are logged on as “system” Special Access Technical Manual v1.0 25 Working in a C Command Prompt? If you are working in a C Prompt, not an SQL Command, follow these instructions to ensure a successful login. To login to the database as “system”, copy and paste the highlighted code below into the Command Prompt window. c:\>sqlplus system/[password]@localhost:1421 If the Command Prompt does not begin with C:\> as shown above, type the following code: >cd c:\ This will change the directory to the C: drive and you can continue following the instructions. Insert the system password created when installing Oracle Express (see step 3.2.6.4) in place of [password]. You don’t need to be in C:\ for this to work. Do not include brackets [ ] before and after the System Password. Do not add a semicolon at the end of the line. Once the code is properly entered, press Enter. The Command Prompt should display information similar to the screenshot below. Figure 20 – System User Log In You are now logged into the database as the system user. This essentially means you are the administrator of the database. You are logged on as “system” Special Access Technical Manual v1.0 26 Tip When entering code into all command prompts, make sure to include the semi- colon at the end of the line. Tip Before modifying the table size, verify you are logged in as the “system” user. To verify you are logged in as the system user, enter the following code: SQL> show user The system will display the name of the user. See section 6.2 for a screenshot of the result of the above code. Designating the .dmp File Location To prepare for final download in step 4.3.19, you need to designate where the .dmp file is created. Please follow the steps below. 3.3.2.3 Enter the highlighted text below into the SQL Command Prompt: SQL>CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS ’c:\’; 3.3.2.4 Enter the highlighted text below to give the system the access needed to put the information in the DMP file. SQL>GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO SPADC; After entering the above code, you should see information similar to the below screenshot in the Command Prompt. Figure 21 – Granting Read/Write Access 3.3.3 To maximize the space allocated for your file information in Oracle Express, enter the highlighted text below: SQL> alter tablespace system add datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM01.DBF' size 1g autoextend on next 1g maxsize unlimited; Note: You may need to update the location in the text above to match where you have installed Oracle Express. You should be able to navigate to the “XE” folder. The command above will create the file “SYSTEM01.DBF” – it will appear in the “XE” folder after the command is completed. 3.3.4 The next step is to create a new user for this database. The user-name and password for this new user are listed below: ID = spadc Password = spadc 3.3.4.1 To create this new “spadc” user, enter the highlighted text below: SQL>create user spadc identified by spadc; 3.3.4.2 Grant the new spadc user permissions by entering the highlighted text below: SQL>grant resource, connect to spadc; You are logged on as “spadc” Special Access Technical Manual v1.0 27 Working in a C Prompt? If you are using a C Prompt (not SQL Command Prompt), instead of following Step 3.3.5.1, connect as the spadc user by copying and pasting the highlighted text below and pressing the Enter key: SQL>conn spadc/spadc@localhost:1521 3.3.5 You will now log into the database as the spadc user that you just created. 3.3.5.1 To log into the database as the spadc user from the SQL Command Prompt, enter the highlighted text below into the SQL Command Prompt and press the Enter Key: SQL> connect Enter user-name: spadc Enter password: spadc You are now logged into the database as the spadc user. 3.3.6 The next step is to create the empty database container shell. In a future step, you will fill this empty shell with your data .txt files you are sending to the FCC. 3.3.6.1 To create the empty database container shell in Oracle Express, enter the following highlighted command then press enter. SQL>@c:\SPADC\spadc_scripts_files\SPADC_DATA_CONTAINER.sql A number of messages will quickly scroll by, as shown below, confirming the execution of each of the sql commands in the “SPADC_DATA_CONTAINER.sql” file. Figure 22 – Creating Database Container You are logged on as “spadc” Special Access Technical Manual v1.0 28 Tip If you have saved the pr_qc_container.sql file in a folder other than the “spadc_scripts_files” folder, make sure the folder name you use does not have any spaces like “sample folder space” as this will result in an error message in performing this step. Figure 23 – Database Container Confirmation 3.3.6.2 Enter the code highlighted below. This code creates the validation procedure that checks the validity of data format of your .txt files when they are loaded into the database container shell: SQL>@c:\SPADC\spadc_scripts_files\pr_qc_container_check.sql If you receive a compilation error, something may be wrong with the spadc user’s permissions in the database. Please see section 6.1 for troubleshooting steps. 3.3.6.3 Once you press ENTER, you should see the following in the SQL Command Prompt: Figure 24 – Successfully Created Procedure Special Access Technical Manual v1.0 29 3.3.6.4 Congratulations! You have successfully loaded Oracle Express and created your database container. You are finished with section 3 of this manual and will not need to revisit those steps. Please follow the steps in section 4 of this manual to complete your submission. Special Access Technical Manual v1.0 30 4 USING THE DATABASE CONTAINER The below steps need to be completed each time you load information into the database container. If you have not set up the database container or the database, please return to section 3 in this manual before proceeding. If you are proceeding to this section, you must first log into the database as the system user. 4.1 LOGGING INTO THE DATABASE To log into the database as the system user from the SQL Command Prompt, enter the highlighted text below into the SQL Command Prompt and press the Enter key: SQL> connect Enter user-name: system Enter password: [The System Password you created in step 3.2.6.4] 4.2 CONVERTING .CSV FILES TO .TXT FILES Only .txt files can be loaded into the GUI and submitted to the FCC. If your tables are in .csv format, please follow the steps below to convert them to .txt files. 4.2.1 Right click on the CSV file you wish to convert over to .txt files (for example IIA17_CP_PCT_REV_GEN.csv). You should see a listing of options. 4.2.2 From the options selection, click “Rename.” This will allow you to edit the name of the file. 4.2.3 Replace .csv with .txt (for example IIA17_CP_PCT_REV_GEN.txt). The following warning message will appear. 4.2.4 Click Yes to complete the conversion. Your file will now be in .txt format. Special Access Technical Manual v1.0 31 4.3 LAUNCHING THE GRAPHICAL USER INTERFACE (GUI) You must know which version of Java you have. To determine which version you have, refer to step 2.2. If you have Java 6, proceed to step 4.3.6. If you have version 7 or higher, you need to edit the GUI prior to launch by following these steps: 4.3.1 Locate the “Launch SPADC.bat” file in the C:\SPADC\ folder. 4.3.2 Right click on the file and select Edit. The Launch SPADC will open in notepad. 4.3.3 Verify that the path location is pointing to the correct jre file as shown in the screen shot below. Figure 25 – Java 6 4.3.4 Replace the “6” with the version of Java you have. For example, the user in Figure 26 has Java 7. Figure 26 – Editing the Java version 4.3.5 Navigate to File and Save. Continue to 4.3.6. To launch the GUI, follow the steps below: 4.3.6 Locate the “Launch SPADC.bat” file in the C:\SPADC\ folder. 4.3.7 Double click on the file to launch the GUI. Note it may take the GUI a moment to load (see the “Database Setup” tab within the GUI for basic instructions regarding setting up the database). Special Access Technical Manual v1.0 32 4.3.8 If your computer is using Java 6, the GUI will appear as in the figure below. Figure 27 – Graphical User Interface (GUI) Special Access Technical Manual v1.0 33 4.3.9 Locate and open the file labeled “spadc.properties” in your C:\SPADC folder. Confirm that the last line in the “spadc.properties” file points to the folder location where the data files are located. 4.3.9.1 You do this by first double clicking the “spadc.properties” file, which brings you to the screen shown below. 4.3.9.2 If the computer asks you to first select a program for opening the file, select “Notepad” from the list of programs. Figure 28 – Locating the SPADC.Properties File 4.3.10 Verify the last 3 lines in the “spadc.properties” file (username, password, location) are accurate. Figure 29 – Verifying Information Within the Properties File Special Access Technical Manual v1.0 34 4.3.11 Note: If the files you intend to load into the database container are not in the (C:\SPADC\spadc_scripts_files) folder: 4.3.11.1 Change the last line of text by simply moving the cursor to the relevant place and typing the information to indicate the correct folder. Do not put any spaces in the file name that you use as this will create problems when trying to reference the folder in the prompt commands. 4.3.11.2 Once changed, you will need to select “File” at the top left corner of the window and then select “Save.” 4.3.12 File Names Save the .txt files that you intend to load into container using GUI in the C:\SPADC\spadc_scripts_files folder. Each .txt file MUST be saved with the applicable name exactly as specified in section 5.1 at the end of this document. The GUI looks for files with these specified names for loading. If you use a different name, the GUI will not function properly as it will not recognize and load your .txt file to into the correct table structure in the container. Note: If you want test the container using sample data, you can save the sample data files (see link to .zip folder on the Database Container download page, http://www.fcc.gov/encyclopedia/database-container) into the spadc_scripts_files folder. However, remember to replace these sample files with your actual data in the container prior to submitting the container to the FCC. 4.3.13 Select “Load All Data” When you are ready to load your .txt files into the container, you can either: Select the “Load All Data” button to load all files. OR Select and load individual files (to select multiple files press hold down the control button when selecting each file). Please see section 4.3.18 for more information about loading individual files. Special Access Technical Manual v1.0 35 Tip To see the GUI loading in real time, select the command prompt icon and the bottom of your screen. Figure 30 – Loading Select Files Into GUI Note: There will likely be a slight delay in performing the “Load All Data” step. The first time you do this you might also receive a pop-up message asking you to identify the directory folder where to find the .txt or .csv files to be loaded. In the example in the following screenshot, you would go to c:\SPADC\spadc_scripts_files. Figure 31 – Identifying the Directory Folder Special Access Technical Manual v1.0 36 4.3.14 When all data is loaded, a window at the bottom of the screen will populate. For additional details about error messaging, see section 4.4. 4.3.15 This message highlighted in the screenshot below indicates the files loaded into the GUI were formatted properly but does not ensure that all necessary questions were answered. Figure 32 – Completed GUI Load Message If you receive an error message, please see section 4.4 for guidance. 4.3.16 See the below screenshot for a detailed example of the GUI load message. Figure 33 – Completed GUI Load Message Detail Please see the information below for an explanation of pieces of the QC Log code. Special Access Technical Manual v1.0 37 4.3.16.1 FRN – The filer’s FRN 4.3.16.2 VALIDATION_RESULT - The data load will validate if the tables were loaded with proper data 4.3.16.3 CP_IND - Indicates whether the filer is a Competitive Provider 4.3.16.4 CP_CABLE_SYS_OPR_IND –You can disregard the entry in this field; it serves no purpose. 4.3.16.5 ILEC - Indicates that the filer is an ILEC 4.3.16.6 BEST_EFF - You can disregard the entry in this field; it serves no purpose. 4.3.16.7 PURC_MW - Indicates whether the filer is specifically a Mobile Wireless Purchaser 4.3.16.8 PURC_OTHER - Indicates whether the filer is specifically an Other Purchaser 4.3.17 Once files have been successfully loaded, the GUI generates a QC Log File and inserts it at the location noted in last line of the “spadc.properties” file, which was referenced in step 4.3.10 above. An example of a newly generated QC Log File is shown in the below screenshot: Figure 34 – QC Log File Location 4.3.18 Want to reload or update a specific file? You can reload specific files using the GUI by selecting the updated tables from the “Table List” and then selecting the “Load Selected Table(s)” button. Please note that every time you select “Load Selected Tables,” or “Load All Data,” a new QC log file is created. Special Access Technical Manual v1.0 38 Figure 35 – Loading Selected Tables into GUI 4.3.19 The last step in using GUI is to go to the “Database Setup” tab and select the “Data Dump” button to create a .dmp file. This will result in the creation of a file named “SPADC.DMP” on your C:\ drive as shown in the screenshots below: Figure 36 – Database Setup Tab Special Access Technical Manual v1.0 39 Figure 37 – Location of DMP File 4.3.20 Once the GUI has created a .dmp file, you will need to change the file type to a .zip. You do this by right clicking on the .dmp file, hovering the cursor over the “Send to” option, and then clicking “Compressed (zipped) folder.” Figure 38 – Modifying the DMP File Special Access Technical Manual v1.0 40 Figure 39 – New Zip File Location 4.3.21 Now the compressed .zip file is ready. Once the secure website portal is made operational to accept this file, you will use the website portal to upload the .zip file to the FCC. 4.4 LOADING ERRORS If you receive an error message when loading the data into the GUI, please read the following for background information and troubleshooting assistance. 4.4.1 Container PASSED or FAILED during the Quality Check When data is loaded into the container using the GUI, the GUI runs a quality check and indicates whether the container as a whole PASSED or FAILED the quality check. Please refer to step 4.3.16 for detailed information on the quality check. The GUI’s quality check searches for the correctness and presence of all required files. Therefore, if you load individual files into the GUI, even if the files you load are correct, if you have not loaded all required files into the container and you will receive an error message. To get a PASSED condition, the system looks to see if the file named SPADC_FILER_ID_INFO.txt was loaded correctly and if all of the mandatory tables of a particular filer type were all loaded correctly as well. So if you loaded one table for Section II.A. questions, the system will look to see if the SPADC_FILER_ID_INFO.txt file and all other mandatory tables in Section II.A. were loaded correctly before giving a PASSED condition. Special Access Technical Manual v1.0 41 Likewise, if you had all of the Section II.A. tables correctly loaded and one Section II.F. table correctly loaded, you would get a FAILED condition until all requisite Section II.F. tables are correctly loaded. 4.4.2 Mandatory tables for the individual filer types are as follows: 4.4.2.1 Competitive Provider IIA1_CP_AFFILIATED_COM.txt IIA2_CP_FAC_ADDL_INFO.txt Note: If the IIA2_CP_FAC_ADDL_INFO.txt file, which should consist of a single row of data with two Y/N columns, contains an “N” in either column, then the remaining .txt files noted below in this section are not mandatorily required to obtain a PASSED condition. Otherwise, the remaining files are required. IIA3_CP_LOCATION_CNT.txt IIA4_CP_LOCATION_DATA.txt IIA7_COLOC_WIRE.txt IIA9_CP_HQ_AC.txt IIA9_CP_HQ_ACQ_D.txt IIA9_CP_HQ_AFF_EF.txt IIA12_CP_PRICE_CE_PART1.txt IIA12_CP_ID_PURC_PART2.txt IIA13_CP_BILLING_ADJ.txt IIA15_SALES_REV_CBDS.txt IIA16_SALES_REV_PBDS.txt IIA17_CP_PCT_REV_GEN.txt 4.4.2.2 ILEC IIB1_ILEC_AFFILIATED_COM.txt IIB2_ILEC_LOCATION_CNT.txt IIB3_ILEC_LOCATION_DATA.txt IIB4_ILEC_PRICE_CE_PART1.txt IIB4_ILEC_PURC_ID_PART2.txt IIB5_ILEC_BILLING_ADJ.txt IIB6_ILEC_BILLING_DESC.txt IIB7_ILEC_COLOC_WIRE.txt IIB8_SALES_REV_CBDS.txt IIB9_SALES_REV_PBDS.txt IIB10_SALES_REV_OMT.txt IIB11_CUST_CNT_OMT.txt Special Access Technical Manual v1.0 42 Tip Max Error Count. The number of rows reported with errors in the .log and .bad files is limited. This means that once the system determines that a set number of rows were not loaded correctly (i.e., a max count of 50 rows), then the system will stop loading the remaining rows in the .txt file. The .log and .bad files will only show rows with errors up to the max count in the table. If you correct all rows noted in the .log and .bad files and attempt to reload the table file, additional errors may be noted in the newly created .log and .bad files that were generated when reloading. IIB12_ALL_TARIFFS.txt IIB13_ILEC_NON_TARIFF.txt 4.4.2.3 Purchaser - Mobile Wireless IIE1_MW_SITES_CNT.txt IIE2_MW_LOCATION_DATA.txt IIE3_TOT_EXP_DS.txt IIE15_MW_NON_TARIFF.txt 4.4.2.4 Purchaser – Other IIF1_PURC_NATURE_BUS.txt IIF2_EXP_DS.txt IIF14_PURC_NON_TARIFF.txt 4.4.3 Error Messages Associated with Individual Tables A separate validation check is run on each .txt table file as it is loaded into the database container shell using the GUI. If formatting errors are detected when loading, the GUI will create a .log file and .bad file in the “spadc scripts and files” folder. The .log file and .bad file will have the same name as the name of the .txt table file that you were trying to load, as shown in Figure 40 below. If you attempt to reload the same table with corrected data, the previously generated.log file and .bad files will be deleted automatically. If new errors are discovered, then new .log and .bad files are generated. The specific information listed in the .log file and .bad file are noted below: .log files show the rows, identified by row numbers, that were incorrect and the reasons why these rows are not loading properly .bad files lists the actual content of the rows that did not successfully load into the database container If information is not loading into the GUI, it is recommended that you first review the information in the .log files to determine which row(s) within the tables did not load correctly and why. If additional information is still needed, the specific code in the .bad files can be used as a reference. Please see the following screen shots and instructions on how to view the information in the .log and .bad files. Special Access Technical Manual v1.0 44 4.4.5 If needed, you can use the .bad files as a reference to see the contents of the incorrect rows, as shown in Figure 42. This information may be useful in correcting the contents of the rows that did not load successfully. Figure 42 – Errors in .bad file Special Access Technical Manual v1.0 45 Tip You are only able to load .txt files into the database container. (See the instructions on converting .csv files to the txt file format that are provided in Section 4.2) 5 REFERENCE INFORMATION This reference section contains additional information regarding the tables and their contents that are loaded into the Graphic User Interface (GUI) for submission to the FCC. 5.1 GUI FILE NAMES AND COLLECTION QUESTIONS The following is a list of file names that respond to the questions for which the database container covers. The .txt files you create must use the applicable file name indicated below. Failure to use these file names exactly as indicated will result in the receipt of an error message during the loading process using the GUI. File Name Corresponding Collection Question to which File is Responding (as indicated in instructions) SPADC_FILER_ID_INFO.txt This file does not relate to a specific data collection question and is used to assist in generating the qc_file.log. The format specification for this file follows this table. IIA1_CP_AFFILIATED_COM.txt Question II.A.1: Affiliated Company IIA2_CP_FAC_ADDL_INFO.txt Question II.A.2: Facilities Requiring Additional Information IIA3_CP_LOCATION_CNT.txt Question II.A.3: Locations Data for Competitive Providers IIA4_CP_LOCATION_DATA.txt Question II.A.4: Locations Data for Competitive Providers IIA7_CP_COLOC_WIRE.txt Question II.A.7: Collocations by Wire Center IIA9_CP_HQ_AC.txt Question II.A.9.a-c: Record Format for U.S. Headquarters IIA9_CP_HQ_ACQ_D.txt Question II.A.9.d: Record Format for Headquarters of Acquired Entities IIA9_CP_HQ_AFF_EF.txt Question II.A.9.e-f IIA11_CP_INFO_RFP_PART1.txt Question II.A.11: Information on Requests for Proposals (RFPs) IIA12_CP_ID_PURC_PART2.txt Question II.A.12-14: Billing Information Overview IIA12_CP_PRICE_CE_PART1.txt Question II.A.12: Prices by Circuit Element from Competitive Provider IIA13_CP_BILLING_ADJ.txt Question II.A.13: Billing Adjustments IIA14_CP_BILLING_DESC.txt Question II.A.14: Billing Code Descriptions IIA15_SALES_REV_CBDS.txt Question II.A.15: Sales Revenues for CBDS Special Access Technical Manual v1.0 46 File Name Corresponding Collection Question to which File is Responding (as indicated in instructions) IIA16_SALES_REV_PBDS.txt Question II.A.16: Sales Revenues for PBDS IIA17_CP_PCT_REV_GEN.txt Question II.A.17: Percentage of Revenues Generated from Agreement or Tariff Containing a Prior Purchase-Based Commitment IIB1_ILEC_AFFILIATED_COM.txt Question II.B.1: Affiliated Company IIB2_ILEC_LOCATION_CNT.txt Question II.B.2: ILEC Locations Data for ILECs IIB3_ILEC_LOCATION_DATA.txt Question II.B.3: Locations Data for ILECs IIB4_ILEC_PRICE_CE_PART1.txt Question II.B.4-6: Billing Information Overview IIB4_ILEC_PURC_ID_PART2.txt Question II.B.4: Prices by Circuit Element from ILECs IIB5_ILEC_BILLING_ADJ.txt Question II.B.5: Billing Adjustments IIB6_ILEC _BILLING_DESC.txt Question II.B.6: Billing Code Descriptions IIB7_ILEC_COLOC_WIRE.txt Question II.B.7: Wire Centers - CLLI Codes - Regulatory Treatment IIB8_SALES_REV_CBDS.txt Question II.B.8: Sales Revenues for CBDS IIB9_SALES_REV_PBDS.txt Question II.B.9: Sales Revenues for PBDS IIB10_SALES_REV_OMT.txt Question II.B.10: Revenues from One Month Term Only Rates IIB11_CUST_CNT_OMT.txt Question II.B.11: Number of Customers with One Month Term Only Rates IIB12_ALL_TARIFFS.txt Question II.B.12: All Applicable Tariff Plans and Contract-Based Tariffs IIB13_ILEC_NON_TARIFF.txt Question II.B.13: Non-Tariffed Agreements IIE1_MW_SITES_CNT.txt Question II.E.1: Total Number of Cell Sites IIE2_MW_LOCATION_DATA.txt Question II.E.2: Cell Site Locations IIE3_TOT_EXP_DS.txt Question II.E.3: Total Expenditures on Dedicated Services IIE4_EXP_ILEC_TARIFF.txt Question II.E.4: Expenditures with ILECs under Tariffs IIE5_EXP_CP_TARIFF.txt Question II.E.5: Expenditures with Competitive Providers under Tariffs IIE6_EXP_ILEC_CP_NON_TARIFF.txt Question II.E.6: Expenditures with ILECs and Competitive Providers Pursuant to Non- Tariffs IIE7_EXP_PBDS_TARIFF.txt Question II.E.7: Expenditures under Tariffs Special Access Technical Manual v1.0 47 File Name Corresponding Collection Question to which File is Responding (as indicated in instructions) for PBDS IIE8_EXP_PBDS_NON_TARIFF.txt Question II.E.8: Expenditures under Non- Tariffs for PBDS IIE14_TARIFFS_PURC_SRVC.txt Question II.E.14: Tariffs under which you Purchase Service IIE15_MW_NON_TARIFF.txt Question II.E.15: Non-Tariffed Agreements IIF1_PURC_NATURE_BUS.txt Question II.E.1: Total Number of Cell Sites IIF2_EXP_DS.txt Question II.F.2: Total Expenditures on Dedicated Services IIF3_ILEC_TARIFF.txt Question II.F.3: Expenditures with ILECs under Tariffs IIF4_CP_TARIFF.txt Question II.F.4: Expenditures with Competitive Providers under Tariffs IIF5_EXP_ILEC_CP_NON_TARIFF.txt Question II.F.5: Expenditures with ILECs and Competitive Providers Pursuant to Non- Tariffs IIF6_EXP_PBDS_TARIFF.txt Question II.F.6: Expenditures under Tariffs for PBDS IIF7_EXP_PBDS_NON_TARIFF.txt Question II.F.7: Expenditures under Non- Tariffs for PBDS IIF13_TARIFFS_PURC_SRVC.txt Question II.E.13: Paying One Month Term Only Rates IIF14_PURC_NON_TARIFF.txt Question II.F.14: Non-Tariffed Agreements Special Access Technical Manual v1.0 48 5.2 TABLE SPECIFICATION FORMAT FOR THE SPADC_FILER_ID_INFO.TXT FILE The table below details the specific formats required for each of the data tables. Table SPADC_FILER_ID_INFO Record Format for SPADC Filer Identification Information Field Name Description Type (limit) Example Filer_FRN FCC Registration Number identification for the Filer Char (10) 0008402202 Filer_Name Company name associated with the FCC Registration Number VarChar2(100) Local Fiber Incorporated Special Access Technical Manual v1.0 49 5.3 OPTIONAL FIELDS The fields in the table below are optional and can be left empty (i.e., null) in the database container without receiving an error message at validation. Mandatory fields requiring a number must be greater than or equal to zero (0). Table Field Name II.A.4 Lat II.A.3 Long II.A.12 Part 1 DSn_Bandwidth II.A.12 Part 1 OtherCBDS_Bandwidth II.A.12 Part 1 PBDS_Bandwidth II.A.12 Part 1 End_Date II.A.12 Part 1 Term_Discount_IC II.A.12 Part 1 Vol_Discount_IC II.A.12 Part 1 Adjustment_ID_1 II.A.12 Part 1 Adjustment_ID_2 II.A.12 Part 1 Adjustment_ID_3 II.A.12 Part 1 Adjustment_ID_4 II.A.12 Part 1 Adjustment_ID_5 II.A.12 Part 1 Adjustment_ID_6 II.A.12 Part 1 Adjustment_ID_7 II.A.12 Part 1 Adjustment_ID_8 II.A.12 Part 1 Adjustment_ID_9 II.A.12 Part 1 Adjustment_ID_10 II.A.12 Part 2 Customer_ID_499 II.A.12 Part 2 Customer_ID_ACNA II.A.12 Part 2 Customer_ID_OCN II.A.13 Contract_ID II.A.13 Adj_Term_Length II.A.13 Adj_Vol_Circ II.A.13 Adj_Vol_Rev II.A.13 Adjustment_Other Note: Can only leave blank if fields Adj_Term or Adj_Vol are Y. Special Access Technical Manual v1.0 50 Table Field Name II.A.14 Billing_Code_Description Note: Can only leave blank if entry in Billing_Code_Desc_Other field. II.A.14 Billing_Code_Desc_Other Note: Can only leave blank if entry in Billing_Code_Description field. II.B.3 Lat II.B.3 Long II.B.4 Part 1 DSn_Bandwidth II.B.4 Part 1 OtherCBDS_Bandwidth II.B.4 Part 1 PBDS_Bandwidth II.B.4 Part 1 End_Date II.B.4 Part 1 Term_Discount_IC II.B.4 Part 1 Vol_Discount_IC II.B.4 Part 1 Adjustment_ID_1 II.B.4 Part 1 Adjustment_ID_2 II.B.4 Part 1 Adjustment_ID_3 II.B.4 Part 1 Adjustment_ID_4 II.B.4 Part 1 Adjustment_ID_5 II.B.4 Part 1 Adjustment_ID_6 II.B.4 Part 1 Adjustment_ID_7 II.B.4 Part 1 Adjustment_ID_8 II.B.4 Part 1 Adjustment_ID_9 II.B.4 Part 1 Adjustment_ID_10 II.B.4 Part 2 Customer_ID_499 II.B.4 Part 2 Customer_ID_ACNA II.B.4 Part 2 Customer_ID_OCN II.B.5 Contract_ID II.B.5 Adj_Term_Length II.B.5 Adj_Vol_Circ II.B.5 Adj_Vol_Rev II.B.5 Adjustment_Other Note: Can only leave blank if Adj_Term or Adj_Vol field is Y. Special Access Technical Manual v1.0 51 Table Field Name II.B.6 Billing_Code_Description Note: Can only leave blank if entry in Billing_Code_Desc_Other field. II.B.6 Billing_Code_Desc_Other Note: Can only leave blank if entry in Billing_Code_Description field. II.E.2 CLLI II.E.4 Expenditures II.E.4 Year II.E.4 Service_Type II.E.4 Agreement_Type II.E.4 Avg_Discount_PPBC II.E.4 5orMore II.E.5 Expenditures II.E.5 Year II.E.5 Service_Type II.E.5 Agreement_Type II.E.5 Avg_Discount_PPBC II.E.5 5orMore II.E.6 Expenditures II.E.6 Year II.E.6 Provider_Type II.E.6 Service_Type II.E.6 Agreement_Type II.E.6 Avg_Discount_PPBC II.E.7 Expenditures II.E.7 Provider_Type II.E.7 Bandwidth II.E.7 Year II.E.8 Expenditures II.E.8 Provider_Type II.E.8 Bandwidth II.E.8 Year Special Access Technical Manual v1.0 52 Table Field Name II.E.14 Outside_Area_Yes_Provider II.E.14 ILEC_Price_Cap II.E.14 ILEC_Price_Cap_Vol II.E.14 ILEC_Price_Flex1 II.E.14 ILEC_Price_Flex1_Vol II.E.14 ILEC_Price_Flex1_Diff II.E.14 ILEC_Price_Flex1_Provider II.E.14 ILEC_Price_Flex2 II.E.14 ILEC_Price_Flex2_Vol II.E.14 ILEC_Price_Flex2_Diff II.E.14 ILEC_Price_Flex2_Provider II.E.14 ILEC_PBDS II.E.14 ILEC_PBDS_Vol II.E.14 ILEC_PBDS_Diff II.E.14 ILEC_PBDS_Provider II.E.14 ILEC_Other II.E.14 ILEC_Other_Service II.E.14 ILEC_Other_Geo II.E.14 ILEC_Other_Diff II.E.14 ILEC_Other_Provider II.E.14 Limiting_Purchase II.F.3 Expenditures II.F.3 Year II.F.3 Service_Type II.F.3 Agreement_Type II.F.3 Avg_Discount_PPBC II.F.3 5orMore II.F.4 Expenditures II.F.4 Year II.F.4 Service_Type II.F.4 Agreement_Type II.F.4 Avg_Discount_PPBC II.F.4 5orMore Special Access Technical Manual v1.0 53 Table Field Name II.F.5 Expenditures II.F.5 Year II.F.5 Provider_Type II.F.5 Service_Type II.F.5 Agreement_Type II.F.5 Avg_Discount_PPBC II.F.6 Expenditures II.F.6 Provider_Type II.F.6 Bandwidth II.F.6 Year II.F.7 Expenditures II.F.7 Provider_Type II.F.7 Bandwidth II.F.7 Year II.F.13 Tar_Plan II.F.13 Tariff_Name II.F.13 TP_Provider II.F.13 TP_Tariff_Number II.F.13 CBT_Plan II.F.13 CBT_Name II.F.13 CBT_Provider II.F.13 CBT_Tariff_Number II.F.13 Tariff_Type II.F.13 Contains_Term II.F.13 Contains_Vol II.F.13 Contains_NonR II.F.13 NonRate II.F.13 Tariff_DS1 II.F.13 Tariff_DS3 II.F.13 Tariff_PBDS II.F.13 Tariff_Other II.F.13 Geo_DS1 II.F.13 Geo_DS3 Special Access Technical Manual v1.0 54 Table Field Name II.F.13 Geo_PBDS II.F.13 Geo_Other II.F.13 Prior_P II.F.13 Outside_Area II.F.13 Outside_Area_Yes II.F.13 Outside_Area_Yes_Diff II.F.13 Outside_Area_Yes_Provider II.F.13 ILEC_Price_Cap II.F.13 ILEC_Price_Cap_Vol II.F.13 ILEC_Price_Flex1 II.F.13 ILEC_Price_Flex1_Vol II.F.13 ILEC_Price_Flex1_Diff II.F.13 ILEC_Price_Flex1_Provider II.F.13 ILEC_Price_Flex2 II.F.13 ILEC_Price_Flex2_Vol II.F.13 ILEC_Price_Flex2_Diff II.F.13 ILEC_Price_Flex2_Provider II.F.13 ILEC_PBDS II.F.13 ILEC_PBDS_Vol II.F.13 ILEC_PBDS_Diff II.F.13 ILEC_PBDS_Provider II.F.13 ILEC_Other II.F.13 ILEC_Other_Service II.F.13 ILEC_Other_Geo II.F.13 ILEC_Other_Diff II.F.13 ILEC_Other_Provider II.F.13 Limiting_Purchase Special Access Technical Manual v1.0 55 5.4 FIELD CHARACTER LIMITS The below table specifies the character limits for specific fields within the tables. Please note the default character limit for text data fields is 255 characters unless stated otherwise below. Table Fields II.A.11 Part 1 RFP_Name (130) RFP_Description (500) RFP_Bid_Details (500) Other_RFP_Details (500) II.A.12 Part 1 Circuit_ID (50) II.A.12 Part 2 Customer_Name (100) II.A.13 Adjustment_Other (500) II.A.14 Billing_Code (100) Billing_Code_Description (500) Billing_Code_Desc_Other (500) II.B.4 Part 1 Circuit_ID (50) II.B.4 Part 2 Customer_Name (100) II.B.5 Adjustment_Other (500) II.B.6 Billing_Code (100) Billing_Code_Description (500) Billing_Code_Desc_Other (500) II.B.13 Parties (500) Summary (2000) II.E.2 Provider (500) II.E.14 TIP_Tariff_Number (500) Outside_Area_Yes (500) Outside_Area_Yes_Diff (500) Outside_Area_Yes_Provider (500) ILEC_Price_Cap_Vol (2000) ILEC_Price_Flex1_Vol (2000) ILEC_Price_Flex1_Diff (2000) ILEC_Price_Flex1_Provider (500) ILEC_Price_Flex2_Vol (2000) ILEC_Price_Flex2_Diff (2000) ILEC_Price_Flex2_Provider (500) Special Access Technical Manual v1.0 56 Table Fields ILEC_PBDS_Vol (2000) ILEC_PBDS_Diff (2000) ILEC_PBDS_Provider (500) ILEC_Other_Service (500) ILEC_Other_Geo (2000) ILEC_Other_Diff (2000) ILEC_Other_Provider (500) Limiting_Purchase (2000) II.E.15 Parties (500) Summary (2000) II.F.13 TIP_Tariff_Number (500) Outside_Area_Yes (500) Outside_Area_Yes_Diff (500) Outside_Area_Yes_Provider (500) ILEC_Price_Cap_Vol (2000) ILEC_Price_Flex1_Vol (2000) ILEC_Price_Flex1_Diff (2000) ILEC_Price_Flex1_Provider (500) ILEC_Price_Flex2_Vol (2000) ILEC_Price_Flex2_Diff (2000) ILEC_Price_Flex2_Provider (500) ILEC_PBDS_Vol (2000) ILEC_PBDS_Diff (2000) ILEC_PBDS_Provider (500) ILEC_Other_Service (500) ILEC_Other_Geo (2000) ILEC_Other_Diff (2000) ILEC_Other_Provider (500) Limiting_Purchase (2000) II.F.14 Parties (500) Summary (2000) Special Access Technical Manual v1.0 57 6 TROUBLESHOOTING Please see the sections below for information on troubleshooting any issues you may encounter. 6.1 COMPILATION ERRORS If you receive a compilation error, something may be wrong with the spadc user’s permissions in the database. Please use the following steps to log into the database as the system user and delete the spadc user. 6.1.1 To log into the database as the system user from the SQL Command Prompt, enter the highlighted text below into the SQL Command Prompt and press the Enter key: SQL> connect Enter user-name: system Enter password: [The system password you created in step 3.2.6.4] 6.1.2 You will then enter the following highlighted text: SQL> DROP USER spadc CASCADE; 6.1.3 The spadc user has been removed from the database. You will now need to recreate the spadc user and log in as the spadc user by following the steps below: To create this new “spadc” user, enter the highlighted text below: SQL>create user spadc identified by spadc; 6.1.4 Grant the new spadc user permissions by entering the highlighted text below: SQL>grant resource, connect to spadc; 6.1.5 To log into the database as the spadc user from the SQL Command Prompt, enter the highlighted text below into the SQL Command Prompt and press the Enter key: SQL> connect Enter user-name: spadc Enter password: spadc You are now logged into the database as the spadc user. 6.1.6 Please proceed to section 3.3.6 and then complete all subsequent steps. Special Access Technical Manual v1.0 58 6.2 USER IDENTIFICATION If you are ever unclear what user you are currently logged into the system as, enter the following highlighted text into the SQL Command Prompt: SQL> show user The system will display the name of the user, as shown in the screenshot below. Figure 43 – Show User