::: Frequently Asked Questions in DMFAS :::

Answer: To load user-defined reports into Excel, follow these steps:

1. Go to DMFAS 5.2 menu, REPORT > LOANS USER DEFINED REPORTS > GENERATE REPORT.
   
   

2. Query for any existing user-defined report, or create a new one.
   
   

3. Enter a valid DOS file name on the field "File Name". You will note that the field "ASCII File Location" will be automatically filled up with a value such as drive:\PROGRAMS\ASCII.
   
   

4. Press button Save, then button Generate Report. When the report is finally generated, you will receive the message “The report output is in drive:\PROGRAMS\ASCII\filename. Please use any text editor to view it – M 3093”. 
   
   

5. If you have the field Print Parameters clicked, the parameter report will be displayed on the Oracle Reports Previewer. Close the Previewer.
   
   

6. Open Microsoft Excel and select menu option FILE/NEW and select "DMFAS.XLT". Click on "Enable Macros" if asked.
   
   

7. If you do not see the template "DMFAS.XLT", obtain the file from the DMFAS CD (directory \DMFAS52\admin\setup\Program Files\Microsoft Office\templates\) or from other configured workstations. Copy the template to the workstation directory where the Excel templates are located by default (e.g. c:\Program Files\Microsoft Office\Templates). 
   
   

8. Select menu option TOOLS -> MACRO -> MACRO 
   
   

9. Select OPEN_DMFAS_REPORT and click on RUN.
   
   

10. Open the user-defined report file created in step 3 above, from directory P:\ASCII (where P: is the mapped drive for the server directory drive:\PROGRAMS\).
    
    

11. The report should be automatically loaded and formatted for Excel.
    
    

12. To add loan and tranche characteristics on the user-defined report and thus on the final Excel report, you must include those characteristics when you formulate the query in Browser. For instance, to include the loan name and the creditor name on the Excel report, click on the columns NAME and CREDITOR_NAME as displayed fields in Browser so that your resulting query is something like
    
    

13. 'SELECT LOAN_ID, TRANCHE_NO, NAME, CREDITOR_COUNTRYFROM LOAN_TRANCHES WHERE ... ORDER BY ....’
    
    

14. The selected loan/tranche characteristics will then appear on the Excel report after the loan number and tranche number, and before the debt totals.
    
    

Answer: Yes, the DMFAS 5.2 system is certified for Windows 2000. The necessary software is available upon request from the DMFAS helpdesk. The software includes new DMFAS 5.2 programs and the Developer Tools version 1.6.1

Answer: While our initial tests indicate that DMFAS 5.2 can run in Windows XP, we would not recommend the installation of the system in this platform. The primary reason is that Oracle itself cannot certify Oracle 7.3.4, 8.05i, 8.1.5i, 8.1.6i, 8.1.7i and 9i (client server) for Windows XP. 

Answer: The text file from user-defined reports, DSM Plus and World Bank forms 1 and 2 is created on the server, in a directory defined in the UTL_FILE_DIR parameter of the Oracle initialization file, INIT.ORA (e.g. directory D:\PROGRAMS\ASCII\). From a workstation, the text file may be found in p:\ASCII, since the server directory D:\PROGRAMS\ is normally mapped to drive p:. 

Answer: The ORA-20000 error message is caused by the existence of duplicate main participants (creditors, debtors, beneficiaries) for a loan or duplicate economic sectors for a tranche. To fix the problem, the DBA can do the following:

1. Obtain more detailed error messages by updating the user-defined report table directly from SQLPlus: 
   - Connect dmfas
   @p:\dmfas52\admin\create\objects\package\dbpack\debug\do_129.sql
   
   
   

2. From SQLPlus, list down the loans with duplicate main participants and economic sectors:
   - Connect dmfas
   @p:\dmfas52\admin\maintain\mult_pa.sql
   @p:\dmfas52\admin\maintain\mult_eco.sql
   
   

3. Delete multiple participants by following the documents, MULT_PA.DOC (English version) and MULT_PA_SPA.DOC (Spanish version), which are available in directory p:\dmfas52\admin\maintain\. 
   

A DMFAS database administrator needs to know how to prevent disruptions to users that are caused by space problems in the DMFAS database. Below is an outline of the basic steps to follow in order to identify, solve and prevent possible problems that are caused by the lack of assigned physical space. For more detailed information, please see Oracle SQL Reference Guide and Chapter 1 of Oracle’s Server Concepts Manual.

1. Show location of data files and the current physical space allocated
   
   For a detailed list of the Tablespaces’ datafiles, execute script DMS_FILE.SQL (located in X:\DMFAS52\ADMIN\ADHOC). See figure 2, which is an extract from the log file DMS_FILE.LOG, generated by the execution of the script. 
   
   See figure 2, which is an extract from the log file DMS_FILE.LOG, generated by the execution of the script. 
   
   
2. Check information about allocated and free space 
   
   After reviewing the extract, identify the size and data file locations of the Tablespace, and execute two scripts (located in X:\DMFAS52\ADMIN\ADHOC), connecting as user name: System. The first is FREE.SQL, which gives a summary list of free space per Tablespace (see figure 3) and the second is SPACE.SQL, which provides additional information including Percentage Free, based on allocated space (column PCT_FREE shown in figure 4). Then, check the information about allocated space and free space to see if any of the Tablespaces reach their maximum size. As a guideline, the recommended policy is to have 20% to 30% of the allocated space free in order to satisfy future demand. 
   
   

   REPORT RUN: 19 November 02:25 Tablespace files: ======================= FILE_NAME TABLESPACE_NAME BYTES F:\ORADATA\DMS1\DMS01TS.ORA DMFAS 136,314,880 F:\ORADATA\DMS1\DMS01XS.ORA DMFASINX 136,314,880 F:\ORADATA\DMS1\REF01TS.ORA DMFASREF 20,971,520 F:\ORADATA\DMS1\ROLL01TS.ORA ROLLBACK_DATA 136,314,880 F:\ORADATA\DMS1\SYS01TS.ORA SYSTEM 146,800,640 F:\ORADATA\DMS1\TEMP01TS.ORA TEMPORARY_DATA 125,829,120 F:\ORADATA\DMS1\TOOL01TS.ORA TOOLS 10,485,760 F:\ORADATA\DMS1\USER01TS.ORA USER_DATA 10,485,760 F:\ORADATA\DMS1\WRK01TS.ORA DMFASWRK 167,772,160 F:\ORADATA\DMS1\WRK01XS.ORA DMFASWRKINX 167,772,160 10 rows selected. Figure 2: Extract of dms_file.log

    

   TABLESPACE_NAME FREE_BYTES FREE_BLOCKS DMFAS 19324928 2359 DMFASINX 54288384 6627 DMFASREF 10747904 1312 DMFASWRK 111132672 13566 DMFASWRKINX 98213888 11989 ROLLBACK_DATA 83058688 10139 SYSTEM 28065792 3426 TEMPORARY_DATA 125820928 15359 TOOLS 5070848 619 USER_DATA 10477568 1279 10 rows selected.  Figure 3: Output generated by FREE.SQL

   
   

   TABLESPACE_NAME LARGEST_FREE_CHUNK  NR_FREE_CHUNKS SUM_ALLOC_BLOCKS SUM_FREE_BLOCKS  PCT_FRE  DMFAS DMFASINX DMFASREF DMFASWRK DMFASWRKINX ROLLBACK_DATA SYSTEM TEMPORARY_DATA TOOLS USER_DATA  2359 6421 1312 13566 11729 9359 3426 12114 619 1279 1  9   1 1 3   4  1  60 1 1 4160 16640 2560   20480 20480 16640 17920 15360 1280 1280 2359 6627 1312   13566   11989   10139 3426   15359 619   1279 14.18% 39.83%  51.25%  66.24%  58.54%  60.93% 19.12%  99.99%  48.36%  99.92%   10 rows selected. Figure 4: Output generated by SPACE.SQL

    

3. Chose optimal solution corresponding to problem identified. 
   
   A decision on the optimal solution needs to be taken depending on each problem identified, i.e. the reason for the lack of space. The most common situations are illustrated in the following two case scenarios:
   
   
   Case A: Where a large amount of data is entered on a daily basis. 
   
   A possible solution here would involve automating datafile extensions using the following syntax:
   
   
   ALTER DATABASE DATAFILE ‘datafile_filename’
   AUTOEXTEND ON NEXT integer [K | M] MAXSIZE integer [ K | M];
   
   This command, using the Autoextend_clause, enables (or disables) the automatic extension of a datafile.
   
   For example (to enable):
   ALTER DATABASE DATAFILE ‘f:\oradata\dms1\dms01ts.ora’ 
   AUTOEXTEND ON NEXT 20M MAXSIZE 300M;
   
   The above statement tells the database to automatically create a new extent of 20 Megabytes every time its preceding extent is full, up to the specified maximum of 300 Megabytes.
   
   
   To disable specify OFF. For example:
   
   ALTER DATABASE DATAFILE ‘f:\oradata\dms1\dms01ts.ora’ 
   AUTOEXTEND OFF
   
   Using the AUTOEXTEND clause for Rollback or Temporary Tablespaces may significantly increase the size of the datafiles. The space allocated will be internally released in the database but the related datafiles will remain the same size as increased during the process. They will have to be resized manually to release space on the disk (see below).
   
   Case B: Where a large amount of data is transferred on a one-time basis. 
   
   As this will need an increase in the size of the Tablespaces, it is necessary to ensure that there is enough free space for the new data. There are two options for this which are: 
   
   (i) use the autoextend clause (see case B above) or 
   (ii) extend the datafiles manually based on the known required space.
   
   Resizing datafiles manually:
   
   The Alter Database command using the Datafile clause with the Resize parameter will allow the datafile to be increased or decreased, using the following syntax:
   
   ALTER DATABASE DATAFILE ‘datafile_filename’ RESIZE integer [ K | M ];
   
   For example to resize a datafile of the tablespace dmfas: 
   
   ALTER DATABASE DATAFILE 
   ‘f:\oradata\dms1\ dms01ts.ora’ 
   RESIZE 160M;
   
   The command above resizes the datafile dms01ts.ora from its current size of 130M to 160M.
   
   If space where the datafiles are located in the hard disk is running out, use another datafile in a different hard disk with the Alter Tablespace statement and the following syntax:
   
   ALTER TABLESPACE tablespace_name
   ADD DATAFILE ‘datafile_filename’ SIZE integer [ K | M];
   
   This adds to the tablespace tablespace_name a datafile specified by datafile_filename. A datafile to a locally managed on-line tablespace can be added. Be sure the file is not in use by another database.
   
   Adding another datafile in a different hard disk to the tablespace dmfas:
   
   ALTER TABLESPACE dmfas 
   ADD DATAFILE ‘g:\oradata\dms1\dms02ts.ora’ SIZE 30M;
   

Answer: This is a bug in the Oracle Universal Installer from the CD of Oracle 8.1.7i (Standard Ed). As an alternative, copy the whole CD of Oracle 8.1.7i to your hard disk. Search for the file SYMCJIT.DLL on the hard disk and rename the file to SYMCJIT.OLD directly from the copied Oracle 8.1.7i. Be sure to rename all occurrences of SYMCJIT.DLL. Then install Oracle 8.1.7i from the hard disk, clicking on SETUP.EXE.

Answer: There are three ways of backing up the DMFAS database:

• Standard network backup. This is done automatically in the evening of each workday; the database must be shut down using the “immediate” option.
   
• Cold backup of the data files. This copies the physical data files (normally located on drive:\oradata\dms1) after the database is shut down using the “immediate” option.
   
• Full database export. This is a portable backup of the data created from Oracle’s export facility.
  To do a full export, create a parameter file, EXP_FULL.PAR (in directory drive:\oradba\admin\dms1\export), which contains these lines:
  
  FILE=drive:\oradba\admin\dms1\export\DMyymmdd.dmp
  FULL=Y
  GRANTS=Y
  INDEXES=Y
  ROWS=Y
  CONSTRAINTS=Y
  LOG= drive:\oradba\admin\dms1\export\DMyymmdd.log
  
  Then issue this command in DOS:
  exp system/<password> parfile= drive:\oradba\admin\dms1\export\exp_full.par
   

 Answer: The new functional features of DMFAS 5.3 involve:

• Bonds
• Improved reporting and statistics
• Revised classification and coding system
• Local government debt
• Capitalization of interest and deferred payments
• Revolving credit
• Asian Development Fund loans

• Enhanced security
• Improved error handling
• Increased sizes of fields such as amounts and sequences
• Extension of user-defined fields to tranche-level and participant-level
• Portable reports in PDF, HTML or RTF formats

Please refer to DMFAS Information Note no. 1, Release of Version 5.3, for details.