DB2/2 AND DB2/6000 QUESTIONS AND ANSWERS3-1994-00 TORONTO, CANADA Version 3.0 ii DB2/2 And DB2/6000 Questions and Answers CONTENTS ________ PREFACE . . . . . . . . . . . . . . . . . . . . . . . V DB2/2 AND DDCS/2 . . . . . . . . . . . . . . . . . . 1 Communications & DDCS . . . . . . . . . . . . . . . . 1 Utilities . . . . . . . . . . . . . . . . . . . . . 16 Install and Migration . . . . . . . . . . . . . . . 19 Application Development . . . . . . . . . . . . . . 27 Coexistence . . . . . . . . . . . . . . . . . . . . 36 Miscellaneous Topics . . . . . . . . . . . . . . . 38 DB2/6000 AND DDCS/6000 . . . . . . . . . . . . . . 47 Communications & DDCS . . . . . . . . . . . . . . . 47 Install and Migration . . . . . . . . . . . . . . . 50 Application Development . . . . . . . . . . . . . . 51 Miscellaneous Topics . . . . . . . . . . . . . . . 52 FEEDBACK QUESTIONNAIRE . . . . . . . . . . . . . . 55 Contents iii iv DB2/2 And DB2/6000 Questions and Answers PREFACE _______ This document contains information that should be of help to users of Database 2 for OS/2 (DB2/2) and Database 2 for AIX (DB2/6000). In some cases, the solution or answer may be applicable to the previous Database Manager (DBM) products on OS/2, such as Extended Edition Version 1.3 (EE 1.3) and Extended Services Version 1.0 (ES 1.0). It also provides information for our Distributed Database Connection Services customers (DDCS/2 1.0, DDCS/2 2.0, and DDCS/6000 1.1). The information here is presented in the form of questions and answers, or problems and solutions, that have come up for other customers. Naturally, due to the ever-increasing complexity of products and the environments in which they are used, it is not always possible for a solution to be applicable in all cases of a problem. If a solution here does not meet your needs, continue to use whatever service process you regularly use, or call: o "1-800-992-4777" for DB2/2 and DDCS/2 o "1-800-225-5249" for DB2/6000 and DDCS/6000 For your convenience we have included BookManager READ/2, which allows you to view this book online. One of BookManager's features is the searching facility, which enables you to search the entire document or any part of it for key words. Another useful feature is the printing facility, which enables you to print off any portion of the document. It is our intention to have this package updated quarterly. We would like to thank you for using our database products, and we hope that the information here will be of assistance to you. Tom Curtis Manager, Workstation Database Service Preface v vi DB2/2 And DB2/6000 Questions and Answers DB2/2 AND DDCS/2 ________________ COMMUNICATIONS & DDCS _____________________ 1. PROBLEM / QUESTION When I try to connect to DB2/MVS with DDCS/2, I'm getting an SQL30080 error with sense data 0806002B. SOLUTION / ANSWER The parameter ACTIVATE_AT_START_UP is set to YES in NDF file, when it should be set to NO. Change this parameter and try again. 2. PROBLEM / QUESTION The DB2/2 1.0 client is accessing a DB2/MVS host database via an ES 1.0 DDCS/2 gateway, and cannot bind the DB2/2 bind files to the DB2/MVS database. SOLUTION / ANSWER Copy all DB2/2 bind files in SQLUBIND.LST to the ES 1.0 server, and then run SQLJBIND using SQLUBIND.LST to bind all the DB2/2 .BND files to the host database. 3. PROBLEM / QUESTION I'm getting SQL30080N 0014 on the client when I try to connect to the server. SOLUTION / ANSWER One possible cause is that the server's installation was incomplete. If you have had a successful connection before, and have not installed anything new, then this is not the problem. Check to see if there is a file called IBMLANLK.LST in the OS2\INSTALL\IBMLANLK\ directory. If there is, you need to reboot the server in order for the installation of DB2/2 1.0 to be complete. This file contains instructions that need to be executed in order to handle any files that were locked during installation. DB2/2 and DDCS/2 1 If this does not work, you will have to reinstall DB2/2 1.0. Delete the OS2\INSTALL\IBMLANLK directory and remove DB2/2; then reinstall it. Another possible cause is that NETBIOS support has not been initialized on the database server and/or the client. ( If sufficient resources were available and NETBIOS support has been initialized, you will hear three beeps on the server and one beep on the client during the STARTDBM.) To increase the resources, you will need to increase the values of NAMES, COMMANDS, and SESSIONS in LAPS. 4. PROBLEM / QUESTION I'm getting SQL1042 error after SQLLOGN2 from a DOS window on OS/2 2.x. SOLUTION / ANSWER This may be due to uninitialized NETBIOS resources. Check the following: a. Ensure that NETBIOS support has been installed and configured. This may either be via LAPS in ES 1.0 Communications Manager, or LAPS as part of NTS/2. b. Ensure that there are enough NETBIOS resources. c. Run SETUPVDD as defined in the DB2/2 readme file. 5. PROBLEM / QUESTION I'm getting SQL0204 error when I try to access a table in AS/400. SOLUTION / ANSWER Check how the table is qualified. For a table in AS/400, the qualifier is the COLLECTION ID or the LIBRARY name. For example, table TABLE1 in COLLECTION (or LIBRARY) COLLE CT, the fullname is COLLECT.TABLE1. (It is not the DDCS/2 default qualifier of logon id.) 6. PROBLEM / QUESTION Does DB2/2 provide an Open Database Connection (ODBC) driver? SOLUTION / ANSWER 2 DB2/2 And DB2/6000 Questions and Answers Yes, we provide first level ODBC support in our Client Application Enabler products as well as our Software Developer's Kit products. The products are currently available for DOS, Windows and OS/2, including this support. 7. PROBLEM / QUESTION My Windows requester gets SQL1032 error when I try to connect, although STARTDRQ and SQLLOGN2 work fine. SOLUTION / ANSWER You need to configure Windows to use virtual NETBIOS. Make sure that in \WINDOWS\SYSTEM.INI, under section 386ENH, the parameter NETWORK is set to *VNETBIOS. 8. PROBLEM / QUESTION What protocols are required for DB2/2 and DDCS/2 to get to any database on the host? SOLUTION / ANSWER From the DB2/2 client to the DDCS/2 server, either NETBIOS or APPC/APPN. To access a host database you need to use APPC. 9. PROBLEM / QUESTION Does DB2/2 support EE 1.3 clients? SOLUTION / ANSWER Yes, but only through the APPC communications protocol. 10. PROBLEM / QUESTION What is the memory requirement for DDCS/2 clients? SOLUTION / ANSWER Refer to the :hpInformation and Planning Guide, p. 40. For DDCS/2 be sure to add 200K to the 230K that is required for a RDS client, to get t he 430K requ irement of a client going through a DDCS/2 gateway to the host. 11. PROBLEM / QUESTION DB2/2 and DDCS/2 3 I'm getting a SQL30080 0003-0000004 error with sense data FFFE0113 when 1 trying to connect to DB2/MVS using dependent LU. SOLUTION / ANSWER This is caused by the wrong configuration on both OS/2 and VTAM. In the DDCS/2 gateway, set PARALLEL_SUPPORT in DEFINE_PARTNER_LU to NO. In VTAM, the MODENT parameter must have the following: COMPROT=X'50A5', PSERVIC=X'060200000000000000122C00'. Another possible cause is that the target only supports dependent LU. Put XID=YES in the VTAM definition for PU of the workstation (in addition to LOCADDR=0). 12. PROBLEM / QUESTION I'm having problems accessing an AS/400 database. SOLUTION / ANSWER This is probably caused by not declaring the RDB directory correctly on the AS/400. Use the ADDRDBDIRE command to define a name for the local AS/400 database. Also, you must use Query Manager/400 to create a NULLID collection. 13. PROBLEM / QUESTION I'm getting SQL30080 with sense data FFFF0003. SOLUTION / ANSWER This indicates that a session activation failed because APPC could not find a link to connect it to the partner node. Define the correct Partner LU Name in Link. Define a link 'to primary host' in CM/2 or ES 1.0 Communications Manager. 14. PROBLEM / QUESTION I'm getting SQL30080 0003-00000004 with sense data 08970015. SOLUTION / ANSWER 4 DB2/2 And DB2/6000 Questions and Answers This is caused by a System Definition Mismatch. Change the PU definition at the host to state: Dynamic LU = YES. 15. PROBLEM / QUESTION Connecting to the SQL/DS machine returns SQL30080 0010-00000000 with sense data 08640001. SOLUTION / ANSWER The SQL/DS server machine was not brought up with the correct parameter to allow DRDA protocol. When bringing it up use: SQLSTART DB(dbname) parm(protocol=AUTO) or SQLSTART DB(dbname) parm(protocol=DRDA). 16. PROBLEM / QUESTION I'm having problems getting DBM DROP DATABASE for a host database working properly. SOLUTION / ANSWER If you want to execute a DROP DATABASE against any host database, you must write an application (in C or another language) to prepare the statement, and then execute it. 17. PROBLEM / QUESTION I'm getting SQL30080 with sense data 081C0103. SOLUTION / ANSWER This is usually caused by the incorrect destination address in the LINK configuration of the client. Check this and if necessary, change it to the correct one. 18. PROBLEM / QUESTION I'm receiving unreadable EBCDIC data when selecting from an OS/400 database via DDCS/2. SOLUTION / ANSWER Usually this is caused by the CCSID being set to 65535. Change the CCSID to 037. DB2/2 and DDCS/2 5 You could also change the character set of the file in question. 19. PROBLEM / QUESTION I'm having problems connecting to DB2/MVS through DDCS/2. First I received SQL30080 0003-00000004; then I received SQL30080 0003-00000005 with sense data 08210002. SOLUTION / ANSWER a. The target database was not the location name of the DB2/MVS database. b. MODTAB of the APPL definition does not contain IBMRDB and SNASVCMG modes. c. Try redefining the target database and define IBMRDB and SNASVCMG in MODTAB. 20. PROBLEM / QUESTION Only one user can connect to DB2/MVS. SOLUTION / ANSWER This is usually caused by missing Mod table definitions on VTAM, so define the correct Mod tables on VTAM and retry. It also may be caused by MAXREMOTE=1 on the DSNTIPE panel of DB2 DDF install or MAXSESSIONS=1. Increase one or both of these as needed. 21. PROBLEM / QUESTION Can I run WINDOWS/DOS client applications on the same machine as the server without going through a network? SOLUTION / ANSWER No. The request has to go through the network. DB2/2 does not support internal loopback. If a network is defined, then the client and server can be on the same physical machine. If you do not have a network, you can attach a Token-Ring wrap plug to your Token-Ring adapter card. Then you will be able to use the Windows/DOS client applications. 6 DB2/2 And DB2/6000 Questions and Answers 22. PROBLEM / QUESTION I'm getting SQL0969 with -7008 as sqlerror when trying to access an AS/400 Database using DDCS/2. SOLUTION / ANSWER Journalling is probably turned off. Journalling must be ON when using DDCS/2 with an isolation level other than Uncommitted Read (UR). To turn journalling on perform the following steps: a. Create a journal receiver: CRTJRNRCV b. Create a journal: CRTJRN c. Start journalling the physical file: STRJRNPF Use an isolation level of uncommitted read (UR). To do this perform either of the two following actions: a. Rebind the application using the /I=UR parameter. b. Use CLI with the command: DBM CHANGE SQLISL TO UR. 23. PROBLEM / QUESTION My DB2/2 client receives logon failed when performing a logon to a DB2/2 Server, using a USERID and PASSWORD defined in UPM on the server and I'm using the APPC communications protocol. SOLUTION / ANSWER The Conversation Security is not being set correctly in CM/2. Define 'Conversation Security' to 'Utilize User Profile Management' (select the push button on the CM/2 SNA Features Panel), or enter the userids and passwords that you want CM/2 to accept (using the same panel). 24. PROBLEM / QUESTION My logon fails with SQL30080 error (APPC code is 080F6051) when I try to connect to a DB2/MVS database using dependent LU. SOLUTION / ANSWER DB2/2 and DDCS/2 7 Try bringing down DB2/MVS and DDF, then bring them back up. 25. PROBLEM / QUESTION Can you use SELECT statements to select different members of an AS/400 physical file (table)? SOLUTION / ANSWER No, not even SQL/400 can do that. Only the first member can be selected. (There may be some techniques on the AS/400 where you can "fool" the system by using an alias name for each member, but we are not aware of any) 26. PROBLEM / QUESTION The Windows client cannot talk to the DB2/2 server, although the DOS-only client application works fine. SOLUTION / ANSWER This is caused by not setting up Windows to point to PC LAN support. Copy MSNET.DRV from Windows diskette 1 to WINDOWS\SYSTEM. Run Windows Setup; choose OPTIONS, then Network. You can then choose PC LAN Support program to be installed. 27. PROBLEM / QUESTION I'm receiving communication error SQL30080 0003-080F6051 (SECURITY_NOT_VALID) when I try to log on to the DB2/MVS host via DDCS/2. SOLUTION / ANSWER The parameter specification in the VTAM APPL statement that is used when defining the DB2 subsystem to VTAM is incorrect. Set SECACPT=ALREADYV in the VTAM APPL statement and try again. (This is assuming that the userid and password are actually valid on the host. Validity should be confirmed first.) 28. PROBLEM / QUESTION I'm having a problem connecting the desired number of APPC clients, or I find that I cannot connect as many as I used to. 8 DB2/2 And DB2/6000 Questions and Answers SOLUTION / ANSWER This is caused by resource contention between the database demands and the LAN Server demands. Increase 'Max Number of Link Stations' on the database server, or stop using the same machine as a database and a LAN Server. The clients that cannot connect will usually HANG. 29. PROBLEM / QUESTION The DDCS/2 connection hangs in VTAM with PCINIT status. SOLUTION / ANSWER This may be caused by the incorrect format in LOGMODE in VTAM. The continuation line must start on column 16. 30. PROBLEM / QUESTION I'm receiving an SQL30080 error with reason code 0038 or 0014, when I use NETBIOS to connect from a client to a server. SOLUTION / ANSWER There are insufficient NETBIOS resources. Increase the NETBIOS resources in PROTOCOL.INI: specifically, NAMES, SESSIONS, and COMMANDS. You can edit the PROTOCOL.INI file directly and change the values of NAMES, SESSIONS, and NCDS, or you can select LAN Adapter and Protocol Support in ES 1.0 Communication Manager, or execute LAPS.EXE in \IBMCOM, if you have NTS/2 installed. 31. PROBLEM / QUESTION I receive SQL30080 Reason Code 0A while trying to issue a connect from the DB2/2 clients to the DB2/2 server. SOLUTION / ANSWER The number of the remote connections exceeded the configured limit. Increase the value of the NUMRC parameter in the Database Manager Configuration. DB2/2 and DDCS/2 9 32. PROBLEM / QUESTION It looks like DB2/2 is not verifying the CM/2 configuration file when it gets done adding the two DB2/2 TPs. SOLUTION / ANSWER DB2/2 does not verify the CM configuration file after installation. If you look at the DB2/2 README.SQL file, under the heading "DB2/2 Server and CM", it states that you must run the verify. 33. PROBLEM / QUESTION Userids for DOS and Windows Clients cannot have the pound sign (#) as a character, but those in DB2MVS, can. Is there a workaround for this? SOLUTION / ANSWER Yes. In DB2/MVS you can use USERID TRANSLATION FUNCTION to "translate" userids without the pound sign to other userids with it. This workaround has to be done on the mainframe. 34. PROBLEM / QUESTION I received 0003-00000004 or 0003-00000005 on the second EE1.3 client connecting to the DB2/2 server. SOLUTION / ANSWER Both EE1.3 clients are using the same Local LU (for example LU1). Ensure that each client has a unique Local LU. 35. PROBLEM / QUESTION Does SQLSIZE in CFG of DOS/WINDOWS CLIENT have to be the same as SVRIOBLK on the server? SOLUTION / ANSWER No, these never have to be the same in both ES and DB2/2. (Note: In ES, the RQRIOBLK of the OS/2 Requester needs to be the same as SVRIOBLK on the server. This does not apply to DB2/2 any more, and has never applied to DOS/Windows clients). 36. PROBLEM / QUESTION 10 DB2/2 And DB2/6000 Questions and Answers After upgrading the database server from EE1.3 to CM/2. EE1.3 clients clients cannot connect with communications error. The clients are using SQLL00 as the communications protocol. SOLUTION / ANSWER The SQLLOO protocol cannot be used with CM/2. 37. PROBLEM / QUESTION I'm receiving SQL30080-14 error. SOLUTION / ANSWER This may be caused by incorrectly cataloging the workstation on the server. Review your catalog parameters. 38. PROBLEM / QUESTION I'm receiving an APPC error of 0003-00000005 with sense data 08120006 when I try to connect to host via DDCS/2. SOLUTION / ANSWER This error is caused by insufficient resources on the host side. Seek assistance from your host system programmers and VTAM support personnel. 39. PROBLEM / QUESTION I'm receiving SQL30061N when I try to connect to AS/400 via DDCS/2. SOLUTION / ANSWER On AS/400, use command ADDRDBDIRE to add a local database directory entry, and use the name for the TARGET DATABASE in DCS directory. 40. PROBLEM / QUESTION DBM EXPORT hangs or returns SQL1325N when I issue it against the host database. SOLUTION / ANSWER EXPORT hangs because it tries to perform a DBM CONNECT. If you use dependent LU and already have a connection, this connect will hang. The only support export format for host databases is IXF. DB2/2 and DDCS/2 11 41. PROBLEM / QUESTION I'm having trouble getting a Windows client up and running. I'm receiving an SQL30080 error rc=14. SOLUTION / ANSWER Check the nname of the workstation and make sure that it is unique. 42. PROBLEM / QUESTION Receiving SQL30080N FFFF or SQL30081N FFFF on a NETBIOS connection. SOLUTION / ANSWER There are multiple causes for this error: 1. NETBIOS is not installed (LAPS for OS/2 and LSP for DOS) 2. NETBIOS was not initialized during the STARTDBM. You should hear 3 beeps on the server and 1 beep on the client during a STARTDBM. 3. The NNAME defined in the Database Manager Configuration is not defined. 4. The NNAME defined in the Database Manager Configuration is not unique on the network. 43. PROBLEM / QUESTION Poor performance occurring with the DOS Client (ES 1.0 , DB2/2, CAE/DOS or SDK/DOS) when using Lan Support Program Version 1.35 and the device driver DXMT0MOD.SYS. SOLUTION / ANSWER Add the parameter PBA=0 to the device statement in the CONFIG.SYS on the DOS Client. It should look something like this: DEVICE=X:\LSP\DXMT0MOD.SYS PBA=0 ... 44. PROBLEM / QUESTION Are back-level clients and servers supported? SOLUTION / ANSWER Back level clients are supported; however, back-level servers are not. We always support clients for the 12 DB2/2 And DB2/6000 Questions and Answers two previous releases. Unpredictable results can occur when the client is at a higher level than the server. 45. PROBLEM / QUESTION Receiving SQL30080N RC=0014 or SQL30081N RC=0014. SOLUTION / ANSWER This could be caused by one of the following: 1. Insufficient resources allocated to NETBIOS support. You can increase these by increasing the values of SESSIONS, NCBS (COMMANDS) and NAMES in your X:\IBMCOM\PROTOCOL.INI file. You must reboot your machine after changing these values. 2. The NNAME specified in the node directory on the client is not found on the network. 46. PROBLEM / QUESTION Receiving SQL30080N RC=FFFF or SQL30081N RC=FFFF. SOLUTION / ANSWER This is caused by one of three things: 1. NNAME in the DATABASE MANAGER CONFIGURATION is not unique on the network. 2. There were insufficient NETBIOS resources during the STARTDBM to load NETBIOS support. The STARTDBM will still return a return code of zero. You should hear 3 beeps on the server and 1 beep on the client when the NETBIOS resources are loaded. If they were not loaded, you will have to increase the NETBIOS resource setting s in the X:\IBMCOM\PROTOCOL.INI, which are defined by SESSIONS, NCBS (COMMANDS) and NAMES. You will need to reboot the machine after changing these values. 3. The proper software for NETBIOS support was not installed on the machine. For OS/2 machines, you need to install LAPS (Lan Adapter and Protocol Support) provided with NTS/2. For native DOS and Windows machines, you need LSP (Lan Support Program). 47. PROBLEM / QUESTION What levels of network software is required to connect to SQL/DS? DB2/2 and DDCS/2 13 SOLUTION / ANSWER If you are using NCP then option C in the NCP configuration, with VTAM 3.4, is required for DDCS to support independent LUs. 48. PROBLEM / QUESTION Receiving SQL1325N on IMPORT/EXPORT to AS/400. SOLUTION / ANSWER You cannot use Delimited ASCII, as this is unsupported. Only PC/IXF is supported. 49. PROBLEM / QUESTION Receiving SQL0961C when trying to access a database table using Crystal Reports. SOLUTION / ANSWER Increase the values of the SQLSIZE parameter in the DBDRQLIB.CFG file. 50. PROBLEM / QUESTION Receiving an SQL1042 when attempting an SQLLOGN2. SOLUTION / ANSWER Look for LT00042 and PRO0025 errors in lantran.log. This could mean you are using an unsupported ethernet card. 51. PROBLEM / QUESTION Receiving an SQL1042C when attempting to do an SQLLOGN2 from a WIN/OS2 session of an OS/2 requester. SOLUTION / ANSWER This can be caused by not loading the LANPDD.OS2 and LANVDD.OS2 device drivers. Make sure these drivers are specified in your CONFIG.SYS. 52. PROBLEM / QUESTION I am receiving a communication error with sense data of FFFF0003 when I disconnect and reconnect to a remote DB2/2 server? SOLUTION / ANSWER 14 DB2/2 And DB2/6000 Questions and Answers Do not select the FREE UNUSED LINK option in the DLC profile for CM/2 configuration, as it will cause the Logical Link to deactivate when a link is not being used anymore. 53. PROBLEM / QUESTION I am receiving a communication error with sense data of 084B6031 while attempting to connect from the DDCS/2 machine to DB2/MVS. SOLUTION / ANSWER You may be using the Local LU name for the partner LU name in the Workstation Database Directory. After recataloging the Node Directory, System Database Directory, and DCS directory entries the problem should be resolved. 54. PROBLEM / QUESTION Does DB2/2 Version 1.x support the TCP/IP protocol? SOLUTION / ANSWER Not as a server; however, you can use the client portion of DB2/2 to connect to a UNIX-based server (DB2/6000, DB2/HP-UX, or DB2/Solaris) using TCP/IP. 55. PROBLEM / QUESTION Communications error using NETBIOS. Receiving SQL30080N RC=000A or SQL30081N RC=000A. SOLUTION / ANSWER One possible cause of this is mixed service levels of either DB2/2 or LAPS. It is recommended that you install the latest service levels of DB2/2 and LAPS, ensure they both complete successfully, and try again. 56. PROBLEM / QUESTION Receiving error: (MicroSoft) (ODBC.DLL) Drivers SQLALLOCENV failed "#0" (IBM) (CLI DRIVER) SQLSTATE=58004. when trying to access a DB2/2 server via CAE/DOS using the ODBC driver with a Windows application. SOLUTION / ANSWER Ensure that the statement "CODEPAGE=437,850" is included in your AUTOEXEC.BAT. DB2/2 and DDCS/2 15 57. PROBLEM / QUESTION How do I change the number of connections I can have to DB2/MVS through DDCS/2 ? SOLUTION / ANSWER a. In CM/2 change the session limit within the Mode definition. b. The session limit is also defined in the APPLID definition for DB2 subsystem in VTAM. c. Change the MAXDBAT parameter in the DSNZPARM dataset for DB2/MVS. The way this works is VTAM and CM/2 negotiate how many connections can go through DRDA from a communications standpoint. This will be the minimum of #1 and #2 above. Once this is defined, DB2/MVS will only allow the number of connections as defined in #3 above. UTILITIES _________ 1. PROBLEM / QUESTION I'm getting SQL0309N from DB2 (Not null column = Null values) is returned. SOLUTION / ANSWER You are trying to import a table with a Null column to a DB2/2 table whose corresponding column is NOT NULL or is NOT NULL WITH DEFAULT. The target column has to be NULLable. 2. PROBLEM / QUESTION I'm getting SQL1325 when trying to IMPORT with CREATE option to a host database. SOLUTION / ANSWER DDCS/2 only supports IMPORT with the INSERT option. The table must be created before the IMPORT can be performed. 3. PROBLEM / QUESTION I attempt to start QM and access DB server. I'm getting QRW0541C RC:11008 and a 'Database bypass screen' message. SOLUTION / ANSWER 16 DB2/2 And DB2/6000 Questions and Answers There are two statements missing from the config.sys file when the client enabler is installed. Add SET QRWDR=X and SET QRWINST=X:\sqllib, where X is the drive where QM is installed. 4. PROBLEM / QUESTION Can Query Manager be installed on a DOS/WINDOWS client? SOLUTION / ANSWER No. Query Manager is a PM-based program, and will only run in an OS/2 environment. It cannot run on a DOS machine. 5. PROBLEM / QUESTION I'm getting QRW2688W (maximum number of objects exceeded) when I try to list 1,200 tables under the same qualifier in Query Manager. SOLUTION / ANSWER DBM allows 32,767 tables. QM can only list 1,085 tables, due to a limitation of the listbox control in Presentation Manager (PM). This is a permanent restriction to Query Manager. 6. PROBLEM / QUESTION Using personalized startup parameters for Query Manager results in /Parameter (/DAT:, /DQUAL:, /PRO:, etc) invalid characters. SOLUTION / ANSWER Enter the parameters in lowercase. Variables after the colons may be entered in either upper or lowercase (/dat:sample or dqual:USERID). 7. PROBLEM / QUESTION Is it possible to import previously saved queries, forms, and procedure definitions? SOLUTION / ANSWER Yes. The table to export is QRWSYS.QRWSYS_OBJECT. Be sure to export it in IXF format. You may now import the IXF file. 8. PROBLEM / QUESTION DB2/2 and DDCS/2 17 How can I make QM print in landscape mode for wide reports? SOLUTION / ANSWER This depends on the printer. For an IBM printer, you can change the page header to be ascii 27 and ascii 108 (alt 027 and alt 108). This will cause the printer to switch to landscape mode. 9. PROBLEM / QUESTION I couldn't run select * from table where (field = '&1') from anywhere but a prompted query in QM. SOLUTION / ANSWER The &1 tries to find a variable named '1', substitute a variable name for the '1' and specify that this variable has the value of '&1'. 10. PROBLEM / QUESTION How can I update a field of my table with data type "special data type" with Query Manager? SOLUTION / ANSWER This is not supported through QM. The options are to create your own application to do this or use an application with this capability already built in. 11. PROBLEM / QUESTION Receiving an SQL2023N with an error return code of 6896 while running a BACKUP. SOLUTION / ANSWER This could be because you copied the SQLDBDIR file from a Volume Database Directory (x:\sqldbdir\sqldbdir) into the System Database Directory (c:\sqllib\sqldbdir\sqldbdir) in order to work around another problem. The "type" field in the entry for each database in a System Database Directory must be ASCII '0' (indirect) or ASCII '1' (remote). The 'type' field in the entry for each database in a Volume Database Directory must be ASCII '2' (home). The DB2/2 code was detecting this value of '2' in the System Database Directory entry for the database being backed up and consequently ended with the error message and error code. 18 DB2/2 And DB2/6000 Questions and Answers To confirm if this is your problem, issue the OS/2 'type' command to display the contents of the sqldbdir file in your System Database Directory. You should see the database alias, followed by at least one undisplayable character (depending on the length of the alias), followed by the database name, followed by at least one undisplayable character (depending on the length of the name), followed by a '0' (zero). If you see a '2' instead of a '0' use a binary editor and change the ASCII '2' (hex '22') to an ASCII zero (hex '20'). 12. PROBLEM / QUESTION Unable to print a table definition from Query Manager. SOLUTION / ANSWER This could mean you have not defined and activated a profile for your database with the printer nickname specified. 13. PROBLEM / QUESTION Receiving SQL0984 while running REORG SOLUTION / ANSWER This means you are running out of log space and this can be fixed by either increasing your log file size, or the number of log files you have. Change the corresponding values in the database configuration (eg. UPDATE DATABASE CONFIGURATION FOR sample) INSTALL AND MIGRATION _____________________ 1. PROBLEM / QUESTION a. I'm getting SQL1042C during STARTDBM after installing select paks 7010 and 7012. b. I'm getting "lock list was not processed" after installation of DB2/2 or select paks. c. My computer hangs when re-booted following installation. d. I'm getting DBI005E during installation of DDCS/2. DB2/2 and DDCS/2 19 e. I'm getting SYS2070 after installation of DB2/2, the computer also has Lan Server 3.0 with the HPFS386 file system installed. f. I'm getting errors during STARTDBM after installing stacker on my computer. SOLUTION / ANSWER The main solution to these installation problems involves the processing of files that were locked by OS/2. A program called IBMLANLK.EXE is used to replace the files that were locked. This program is executed during the startup processing of the CONFIG.SYS file. Many problems can occur during the execution of this program that result in errors with DB2/2, User Profile Management, or First Failure Support Tool (FFST/2). Check for the existence of the file OS2\INSTALL\IBMLANLK.LST on the drive where OS/2 was installed. If this file exists, then the IBMLANLK.EXE program did not complete successfully during the startup of the computer and you should attempt to execute the IBMLANLK program again. To do so, add the following two statements to your CONFIG.SYS and re-boot: DEVICE=C:\OS2\INSTALL\IBMLANLK.SYS C:\OS2\INSTALL\IBMLANLK.LST RUN=C:\OS2\INSTALL\IBMALANLK.EXE C:\OS2\INSTALL\IBMALNLK.LST where C: is the drive on which OS2 was installed. Note: Be sure that all IFS= statements come before the above statements in the CONFIG.SYS file. Stacker-type products have been known to cause some problems during the processing of the IBMLANLK.LST file. Move the location of the stacker device drivers in the CONFIG.SYS and re-boot the computer. 2. PROBLEM / QUESTION Do I need to reinstall CM/2 after applying the DB2/2 select pak 7010 if DB2/2 was installed after CM/2 1.0? SOLUTION / ANSWER DB2/2 overwrites the UPM with CSD level IP07000, whereas CM/2 installed UPM at a level of IP07001. There is no need to reinstall CM/2, as it will work 20 DB2/2 And DB2/6000 Questions and Answers just fine with the version of UPM that DB2/2 installs. 3. PROBLEM / QUESTION I'm getting DBI0021E during DBCID installation. SOLUTION / ANSWER The 'archive' bit may be 'on' for the subdirectory specified for the /L parameter to the log file subdirectory. This directory is where the CID Installation code will copy the trace log and history information resulting from the installation. Use the 'ATTRIB' command to reset the 'archive' bit. 4. PROBLEM / QUESTION When I install DB2/2 on a drive other than the boot drive, I get DBI0021E and DBI0019E errors. SOLUTION / ANSWER DBI0021E occurs during the determination of the amount of disk space that is available on the installation drive. There could be an older version of DB2/2 installated. Run CHKDSK on the installation drive to verify the integrity of the disk drive. 5. PROBLEM / QUESTION Why do I receive the error 'invalid path or filename "MUGLIB"'? SOLUTION / ANSWER Determine if the path or filename is valid. There should be a directory called MUGLIB on the installation drive. Also use the ATTRIB command to check the attributes as they should be set to directory. 6. PROBLEM / QUESTION What should I do if following migration of an ES 1.0 database I encounter the error SQL5046 "SQLENSEG is too small"? SOLUTION / ANSWER If you change database and/or database manager paraeters (ie. DBHEAP), the SQLENSEG parameter should also be increased. This will not only help with the DB2/2 and DDCS/2 21 SQL5046 condition, but will resolve any configuration incompatiblity problems when migrating databases. 7. PROBLEM / QUESTION When migrating DB from ES1.0 to DB2/2, I receive SQL02519 and SQL05045 error (configuration is not compatible). SOLUTION / ANSWER Run MIGRATE1.EXE from the command prompt after increasing the DB heapsize. 8. PROBLEM / QUESTION I'm getting DBI0050E when installing DDCS/2, although I have the right the right DB2/2 and DDCS/2 versions and installed DB2/2 first. SOLUTION / ANSWER An incorrect IBMLVL.INI file is in the root directory of the boot drive. Replace the IBMLVL.INI with an older IBMLVL.INI. (ie. IBMLVL.BK) 9. PROBLEM / QUESTION What are the memory requirements for DB2/2? SOLUTION / ANSWER DB2/2 server: minimum 2.5 meg; DB2/2 client: minimum 1.7 Meg. (See p. 40 of DB2/2 Information & Planning Guide for more details - S62G-3662) 10. PROBLEM / QUESTION I encountered error message 'DBI0050E The Version of DATABASE 2 OS/2 Installed will not Support this version of DDCS/2' while installing DDCS/2 after I installed DB2/2. SOLUTION / ANSWER If you installed the DB2/2 Single User product and specified 'STANDALONE' support. DDCS/2 cannot be installed. 22 DB2/2 And DB2/6000 Questions and Answers Reinstall DB2/2 Single User and specify either Client or Client with Local Database support. 11. PROBLEM / QUESTION I attempted to do a redirected install, and received error message 'DBI00016E not enough space to perform the install request'. SOLUTION / ANSWER There is not enough disk space on the boot drive to copy temporary files. To use another drive to store the temporary files, refer to the DB2/2 Installation Guide p. 6. 12. PROBLEM / QUESTION I received DBI0052 during the installation of DDCS/2 2.0 on DB2/2 1.0. SOLUTION / ANSWER Check the following: a. Make sure that CM/2 or ES 1.0 Communications Manager is installed. b. Make sure that the APPC.DLL exists in the \CMLIB\DLL\ directory. If it does not, you will need to reinstall Communications Manager making sure that the APPC APIs are installed as one of the options. c. Make sure that if you are installing the Multiuser version of DDCS/2 that you have the Client/Server version of DB2/2 installed. 13. PROBLEM / QUESTION I'm getting DBCID error 1604. SOLUTION / ANSWER The response file being used by DBCID is incompatible. Make sure that when DBCID is run from the directory containing the server code, the response file has the keywords DBxxxx. If DBCID is run from the distributed feature directory, the response file has DCFxxxx keywords. The DBCID command is different for the server and for the distributed client feature. DB2/2 and DDCS/2 23 Note: 1604 is a general error so this is just one possible cause. (refer to DB2/2 Installation guide pages 27-28 and 33-34. 14. PROBLEM / QUESTION I'm getting error LSI0006 during reboot after DB2/2 installation SOLUTION / ANSWER The IBMLANLK program did not working properly (see the first Q and A in this section). Add DEVICE=C:\OS2\INSTALL\IBMLANLK.SYS C:\OS2\INSTALL\ IBMLANLK.LST RUN=C:\OS2\INSTALL\IBMLANLK.EXE C:\OS2\INSTALL\IBMLANLK.LST 15. PROBLEM / QUESTION How do I update the database manager configuration from the command line? SOLUTION / ANSWER Type "dbm update database manager configuration using XXXXXXX ". 16. PROBLEM / QUESTION I'm getting DBI0021E during DBINST remove or install: "Invalid PATH C:\OS2\INSTALL\IBMLANLK". SOLUTION / ANSWER The achive bit for IBMLANLK was probably set, use ATTRIB -A IBMLANKLK to turn it off. 17. PROBLEM / QUESTION I'm getting a codepage error during migration of DB from EE1.3 to DB2/2. SOLUTION / ANSWER The codepage is set on OS/2 via the parameter CODEPAGE. Reset the codepage on the new machine, reboot, and try to migrate the DB again. 18. PROBLEM / QUESTION 24 DB2/2 And DB2/6000 Questions and Answers I am trying to run a Windows DB application from a WIN-OS2 session and connect to a DB2/2 machine. SOLUTION / ANSWER To use Windows applications the DB2/2 server version is required. 19. PROBLEM / QUESTION The performance of a SELECT is very slow. SOLUTION / ANSWER The index may be defined in the wrong logical order for this select. Create a new index with the column being selected as the primary key value. 20. PROBLEM / QUESTION I'm having problem wih CID install while using the example in the installation guide which indicated /R= as a parameter. SOLUTION / ANSWER Use the correct syntax of /R:. 21. PROBLEM / QUESTION I'm trying to create a remote database on the server from the client machine and the client hangs, and the db is not created. SOLUTION / ANSWER You cannot 'easily' create a database from a client. Either create it locally on the server or write a DARI application to create a database that can be invoked from the client. 22. PROBLEM / QUESTION Can I install DB2/2 on a file server and allow users to execute DB2/2 from the file server? SOLUTION / ANSWER No, only one connection is allowed for each DB2/2 user. This will prevent other users from accessing the DBs. Also, it will pose integrity issues if they use the same DB on the LAN. 23. PROBLEM / QUESTION DB2/2 and DDCS/2 25 Why does a DBMBAK directory get created when you perform a CID install? SOLUTION / ANSWER In your CID control file the following line exists, requesting a backup of the existing version of database manager: "DBBackup System=X:\PATH". If you do not want to back up your existing version, or do not have a current version, you do not need this line. 24. PROBLEM / QUESTION Unable to use the default USERID and PASSWORD provided by DB2/2 so that I have a userid set up with SYSADM authority. SOLUTION / ANSWER Copy NET.ACC from DB2/2 installation diskette #1. This will give you the default userid. NOTE: You will lose any previously defined userids for that server. 25. PROBLEM / QUESTION How do I load DB2/2 DLLs only when needed? SOLUTION / ANSWER Use the LOADONCALL Linker option. 26. PROBLEM / QUESTION I am receiving DBI0019E Unexpected PM error WIN1302W. SOLUTION / ANSWER Corrupted IBMLVL.INI file. Rename the file to something else attempt another DB2/2 install. 27. PROBLEM / QUESTION After installing CAE/DOS on a DOS/Windows machine, there was a problem booting the machine. SOLUTION / ANSWER Make sure you are loading LSL.COM using the HIGH /L option as opposed to simply HIGH. What happens is that the two are competing for the same place in memory. 26 DB2/2 And DB2/6000 Questions and Answers 28. PROBLEM / QUESTION DBI0029E received during install of DB2/2 V1.x. SOLUTION / ANSWER One cause of this error is if you copy the install diskettes to your hard drive for install, but do not use the XCOPY with the /s option. There are subdirectories that also must be copied over. APPLICATION DEVELOPMENT _______________________ 1. PROBLEM / QUESTION a. I'm getting SQL0805 with a DB2/2 server and DBM clients while running the DBM command line interface. b. I'm getting SQL0818N when I access a database remotely from the DBM Command Line Processor (CLP) and REXX programs. SOLUTION / ANSWER The command line interface is not self-binding, so the package names can't be be found. Manually bind the .BND files to the database by doing the following: x:\SQLLIB\SQLBIND @SQLUBIND.LST dbname (where x: is the DB2/2 installed drive). 2. PROBLEM / QUESTION a. My Micro Focus program traps in NETAPI.DLL at the STOP RUN statement. b. The UPPDATE.SQC sample program in DB2/2 Programming Guide traps in DOSCALL1.DLL c. I'm getting a TRAP E in SQLT.DLL with DB2/2. SOLUTION / ANSWER The correct LINK options for the supported languages are as follows: 1> CSET/2 link386 /NOI /ST:32000 "objfile",,,sql_dyn 2> MS 6.0 link386 /ST:32000 "objfile",,,sqldyn16.lib DB2/2 and DDCS/2 27 (For the correct syntax see p. 28 of DB2/2 Programming Guide S62G-3665) Note: All known stack-related problems have been resolved in the DB2/2 Select pak 7010. 3. PROBLEM / QUESTION I'm trying to compile the sample program on p. 30-31 of the DB2/2 Query Manager Programming Guide and Reference, and I'm getting an error on the DSQCIC function call. SOLUTION / ANSWER The 'include' file dsqcommc.h has the option CEDCL in the function definition for DSQCIC(), and this option is not recognized by your 'C' compiler. 4. PROBLEM / QUESTION How do I select into SQLDA then insert data from the SQLDA into another table? SOLUTION / ANSWER Be sure that the number of VALUES you are inserting into the table matches the actual number of columns. For example, "INSERT INTO USERID.TABLENAME VALUES(?, ?, ?)". The number of wildcard symbols should be equal to the SQLDA (number of columns). 5. PROBLEM / QUESTION My application was working on ES1.0. When I moved to DB2/2 it TRAPS in SQLR.DLL and my program uses multiple threads. SOLUTION / ANSWER This is caused by not allocating a large enough stack for the spawned thread. Increase the stack to 32k for the DOSCREATETHREAD OS/2 API call. 6. PROBLEM / QUESTION The precompiler generates the following COBOL code: 88 SQL_CONNECT_STMT VALUE1 WHEN SET TO FALSE IS ZERO. SOLUTION / ANSWER 28 DB2/2 And DB2/6000 Questions and Answers You must have MicroFocus V3.0 or higher. Using V3.0 this statement is fine but, if you use an earlier version it will not work. 7. PROBLEM / QUESTION My cobol sample program from the DB2/2 programming guide UPDATE.SQB traps. I'm using MicroFocus COBOL v3.1 compiler. SOLUTION / ANSWER COBLIB is not being linked. Check the link options as follows: link /ST:64000 /PM:VIO update.obj,,,sqldyn16+coblib+doscalls Note: coblib must be linked before doscalls or there will be a trap. 8. PROBLEM / QUESTION I'm getting error SYS2070 "System Cannot Demand Load SQLAK32.SQLAALOC" when running a program I compiled and linked with DB2/2. My application was originally written using ES 1.0 Database Manager. SOLUTION / ANSWER The NOI (No Ignore Case) option is required and MUST be specified when linking a C SET/2 2 32-bit application. (see p. 28 of the DB2/2 Programming Guide). Specify the required link options for C SET/2 32-bit applications. NOI is one required option. You must also link with the DB2/2 32-bit library, SQL_DYN.LIB. 9. PROBLEM / QUESTION Are there application development tools shipped with DB2/2? SOLUTION / ANSWER Yes, all of the required prototypes and libraries are included with DB2/2 Single User and DB2/2 Client Server. 10. PROBLEM / QUESTION DB2/2 and DDCS/2 29 I'm getting sys3175 in SQLAPI.DLL with CS:EIP= 005B:11E3331BDE. SOLUTION / ANSWER You may have linked the application with the 16 bit library, SQLDYN16.LIB. For 32-bit applications the SQLDYN.LIB library must be used during the link stage of application development. 11. PROBLEM / QUESTION My Smalltalk 2.0 application traps in doscall1 (sys3171). SOLUTION / ANSWER Smalltalk 2.0 is an 16-bit application which uses the API REXXAPI.DLL (SQLVAR in particular) which is not converted by DB2/2 to 32 bit. This is a permanent restriction for 16-bit applications which use the REXXAPI.DLL. 12. PROBLEM / QUESTION What host languages does DB2/2 support, and what version of COBOL do I need? SOLUTION / ANSWER 16-bit: Microsoft C v6.0, WATCOM FORTRAN v9.01 level D, MicroFocus COBOL V3.0. 32-bit: IBM C Set/2 v1.0, WATCOM F77/386 v9.01 level D, and REXX. 13. PROBLEM / QUESTION Why do I get SQL0752 when I connect to a database? I use to get SQL1098. SOLUTION / ANSWER DB connections now allow a connect if you are already connected, provided that the application is not in the middle of a unit of work. The application program should be changed because it should not be doing a connect while there is an open unit of work. A commit followed by a connect should be done. 30 DB2/2 And DB2/6000 Questions and Answers 14. PROBLEM / QUESTION I want to print the number of rows being effected by an update by printing the SQLCA.SQLERRD[3] field. SOLUTION / ANSWER In C, arrays are indexed from 0 so the array should be specified as SQLCA.SQLERRD[2]. 15. PROBLEM / QUESTION My COBOL application works if I use the Animator but, it cannot find any SQL functions when I don't use the Animator. SOLUTION / ANSWER You may not be specifying the SQLDYN16.LIB when linking the application. link /ST:64000 /PM:VIO app1.obj,,,sqldyn16; 16. PROBLEM / QUESTION How do I bind applications at runtime? SOLUTION / ANSWER The best way is to have a DBADM or SYSADM bind all of the .BND files to the databases after you install any new application. The other method is to have the application bind using the API SQLABNDR, this will bind the BND files to the database at runtime. (see p. 5 of the DB2/2 Programming Reference S62G-3666) 17. PROBLEM / QUESTION I can't compile my ES 1.0 application in DB2/2 using Microsoft C 6.0. SOLUTION / ANSWER Be sure that you have set environment variables as described on p. 15 of DB2/2 Programming Guide. (see p. 17 for compile options) 18. PROBLEM / QUESTION I'm getting SQL30080 error with APPC code F015-00000000. DB2/2 and DDCS/2 31 SOLUTION / ANSWER Increase your stack size and relink your application. 19. PROBLEM / QUESTION I had authorization problems when using QM APIs. USERID could access everything, but no one else could. SOLUTION / ANSWER The tables were qualified by USERID and thus only USERID could access them. 20. PROBLEM / QUESTION I can't get the DBM command line interface to work from an OS/2 CMD file. I'm using the "\" character to allow multiple lines in the CMD file. SOLUTION / ANSWER Make the file into a REXX command file (/* */ in first line) and use the "," character as the continuation character. 21. PROBLEM / QUESTION Why am I getting a SQLCODE of 0 when I should be getting SQL0100? SOLUTION / ANSWER You are getting the 0 because you are closing the cursor before checking for SQLCODE therefore the SQLCODE is from the CLOSE CURSOR operation not the FETCH. 22. PROBLEM / QUESTION My QM callable interface application will not work. I cannot pass global variable with the date. SOLUTION / ANSWER In the query, you must enclose the &GLOBALVAR with single quotes if the variable is a date or a string. If it is a number, then it is will work without the quotes. 23. PROBLEM / QUESTION 32 DB2/2 And DB2/6000 Questions and Answers My PM application causes the machine to hang while I connect to a DB2/2 database. SOLUTION / ANSWER The connect statement is preceded by a PM windows creation api. This hides the logon panel, but in this case the whole machine hangs. Try moving the connect statement to the begining of the program. 24. PROBLEM / QUESTION I can't get multiple DBM calls from to work from a .CMD file. SOLUTION / ANSWER Prefix each DBM call with 'call DBM ....'. 25. PROBLEM / QUESTION I'm having trouble linking with SQL_DYN.LIB. Do I need to use the SQL_STAT.LIB libraru? SOLUTION / ANSWER You should be linking to SQLDYN16.LIB. The SQL_STAT.LIB is no longer used with DB2/2. It was used in previous releases. 26. PROBLEM / QUESTION How do I use data from an SQL query in a REXX program? SOLUTION / ANSWER Try running the SQL query from the REXX program and then opening a cursor, fetching the data into a host variable until the fetch results in EOF (-100). (see DB2/2 Programming Guide) 27. PROBLEM / QUESTION I created a database and was getting an SQL0805N while trying to access the data. SOLUTION / ANSWER Recreate the database and use QM to connect, the QRW plans will be bound to the database. 28. PROBLEM / QUESTION DB2/2 and DDCS/2 33 Are stored procedures static or dynamic, and where can I find out the package names of stored procedures? SOLUTION / ANSWER They can be static or dynamic based on how the server DLL was created. If the SQL is dynamic there will be no package. If a .SQC file was used, and a .BND file bound to the database, the plans will be found in SYSIBM.SYSPLANS. 29. PROBLEM / QUESTION Why does DB2/2 require so much extra log space when a large unit of work is being performed? SOLUTION / ANSWER Reserved space is allocated for a potential ROLLBACK of a unit of work. This reserved space is logical space that is not used until the ROLLBACK operation would occur. Therefore a SQL0964 error (Log full) will occur unless the required space is available. 30. PROBLEM / QUESTION Why do I have SQL0501 errors occuring? SOLUTION / ANSWER The SQL0501 is being caused by attempting to use a cursor after closing, or closing a cursor after a commit. 31. PROBLEM / QUESTION How can a DDCS/2 application refer to a table on DB2/MVS without having to qualify the table? SOLUTION / ANSWER You need to create an TSO id then prep, bind, and create the table with this ID. Then you must grant access to public. 32. PROBLEM / QUESTION How do I do a backup from a remote machine? SOLUTION / ANSWER Refer to the DARI information in the DB2/2 Programming Guide and Reference. 34 DB2/2 And DB2/6000 Questions and Answers 33. PROBLEM / QUESTION How many table/view references are supported in a single SQL statement? SOLUTION / ANSWER The total number of tables and views that can be referenced in a single SQL statement is 15. 34. PROBLEM / QUESTION Does DB2/2 support the Pascal language? SOLUTION / ANSWER We do not support the Pascal programming language. 35. PROBLEM / QUESTION How can I access another users tables without having to specify the table qualifier every time? SOLUTION / ANSWER Create a view with full authority on the table you wish to access. 36. PROBLEM / QUESTION Can users update long varchar data via REXX, CLP, etc? SOLUTION / ANSWER No, long varchar data can only be updated to the database via a C program. 37. PROBLEM / QUESTION I am receiving the following compile error using a 16 bit compiler: "_SYSTEM" is missing. SOLUTION / ANSWER Use the complile option -DSQL16to32 as documented in the Programming Guide. 38. PROBLEM / QUESTION Can you concatenate two string columns together as part of a SELECT statement and return only one column? DB2/2 and DDCS/2 35 SOLUTION / ANSWER No. This can be done in DB2/MVS but not in DB2/2. 39. PROBLEM / QUESTION How can you see who is holding locks on db tables? SOLUTION / ANSWER Use the utility SQLPLOCK. This utility is on Compuserve in the IBMDB2 forum. If using DB2/6000 V1.0 the database monitor functions may also be used to gather locking information. 40. PROBLEM / QUESTION Why does the DB2 precompiler reject host variables of length greater than 254 when using FORTRAN? SOLUTION / ANSWER This is due to the inability of FORTRAN to handle structure type variables and variable length variables. Workarounds include: a. Use C or COBOL b. Use the SQLDA structure in FORTRAN. c. PROBLEM / QUESTION How do you turn on the USEREXIT parameter in the database configuration from the command line interface? SOLUTION / ANSWER Perform the following CLP command: DBM UPDATE DATABASE CONFIGURATION FOR USING USEREXIT ON COEXISTENCE ___________ 1. PROBLEM / QUESTION I'm getting SQL0804N when to try to connect to a DB2/2 DB. The table was imported from a Lotus Datalens spreadsheet. SOLUTION / ANSWER There is a fix from Lotus for this problem. 36 DB2/2 And DB2/6000 Questions and Answers The fix is now available from Lotus and can be obtained from your closest Lotus Bulletin Board System or contact Lotus Technical support. The package name is DLENSDRV.ZIP under '123 for OS2'. The files must be unzipped into a temporary directory then use INSTALL.EXE to complete the installation of the fix. 2. PROBLEM / QUESTION I'm having problems installing ES 1.0 on OS/2 2.1. SOLUTION / ANSWER There is a workaround for this problem. You must do the following: There's file ESSTART.BAK that comes with OS/2 2.1. Rename the ESSTART.BAK on the first diskette of OS/2 2.1 to ESSTART.CMD and copy it to the OS2/INSTALL directory. Also copy the ESSTART.CMD file to the ES 1.0 installation diskette. 3. PROBLEM / QUESTION I want to know how to use Netware Requester and DB2/2 1. 0. What protocol can I use to setup the client and server? SOLUTION / ANSWER For DB2/2 1.0 use, APPC or NETBIOS protocol. You cannot use the IBM Netware Requester Protocol Driver. LAPS supports Netware Requester for OS/2 coexistence by using the IBM Netware Requester Support Protocol Driver (refer to the co-existence guide S96F-8311). 4. PROBLEM / QUESTION Can DB2/2 1.0 use Novell Netware Communications Protocols? SOLUTION / ANSWER No, but NETBIOS can coexist on the same card with Netware. You can use the APPC supplied with CM/2. NETBIOS comes as part of LAPS with NTS/2, as LAPS (NETBIOS support) is no longer part of Communications Manager. 5. PROBLEM / QUESTION DB2/2 and DDCS/2 37 Can I set up a DB2/2 1.0 server and client on a Novell LAN? SOLUTION / ANSWER Yes. You will need the LAPS (Lan Adapter Protocol Support) portion of NTS/2 (Network Transport Services/2) from IBM installed on both the Server and Client. This is required for NETBIOS support. 6. PROBLEM / QUESTION I'm unable to logon to the LAN after DB2/2 1.0 installation. SOLUTION / ANSWER DB2/2 1.0 updates UPM from version 2.0 to version 3.0 during installation. Reinstall the Lan Requester/Server 2.0 on the system or install Lan Requester/Server version 3.0 on the machine after DB2/2 1.0 has been installed. So as to have consistent versions of UPM and the LAN. MISCELLANEOUS TOPICS ____________________ 1. PROBLEM / QUESTION Why am I getting SQL0101 error? SOLUTION / ANSWER This is caused by insufficient statement heap size in the database configuration. Increase the statement heap size from 64 to 255 (max). The default is 64 in DB2/2 (for server) as it was in ES 1.0. A query that runs in ES 1.0 may not work in DB2/2 even if the statement heap size is the same as DB2/2 has more memory requirements. The SQL0101 may also be caused by referential integrity limitations when processing UPDATEs. 2. PROBLEM / QUESTION I'm getting SQL5046 - sqlenseg is too small. SOLUTION / ANSWER Increase SQLENSEG to the max 8192. 38 DB2/2 And DB2/6000 Questions and Answers The SQLENSEG parameter does not commit the memory unless it is required, therefore setting it at the maximum should not result in memory problems. If other DBM parameters change, you may receive an incompatibility error, so this solution applies in this case as well. 3. PROBLEM / QUESTION What is the default userid and password? SOLUTION / ANSWER The default userid is "USERID" and the default password is "PASSWORD" for UPM SYSADM authority. 4. PROBLEM / QUESTION The OPEN database option is not highlighted, which prevents you from creating a new database on my machine. This is in Query Manager, when you select NEW, which is the top line of the database list, and go to ACTION items. SOLUTION / ANSWER This will happen if you installed the DB2/2 Single User product and selected client only (with no local database support). Type DBINST, select REINSTALL, and specify Client With Local Database support. Load the diskettes as requested, and reboot when the reinstallation procedure is completed. 5. PROBLEM / QUESTION How do I get the DB2/2 and DDCS/2 Select Paks and Service Paks from Compuserve? SOLUTION / ANSWER On Compuserve do the following: a. Go to IBMDB2 b. Choose option #4 Forum OS2DF2 c. Go into libraries section (4) d. Search for the latest service pak, by name. 6. PROBLEM / QUESTION DB2/2 and DDCS/2 39 When do negative return codes appear and how are errors handled? SOLUTION / ANSWER Negative SQL codes will be returned if any SQL function call fails. This return code will be assigned to the SQLCA.SQLCODE structure. It is the application's responsibility to capture the error information. 7. PROBLEM / QUESTION How can I specify the ISO DATE format YYYY-MM-DD when the default DATE format used with COUNTRY USA is MMDDYY? SOLUTION / ANSWER DB2/2 determines the DATE format to be used based on the Country Code in CONFIG.SYS. DB2/2 Installation does not allow the user to specify the DATE format to be used. The DATE is always stored the same way within the actual database. However, each application can specify the format of DATE data passed to it by using the /F=format option with either the SQLPREP or SQLBIND commands. The default format (if no /F option is specified) is based on the COUNTRY code. However, this presents a problem if the customer is using the interactive debug mode supported by Micro Focus COBOL to interactively debug a program. In this case, MicroFocus does an SQLPREP and SQLBIND "under the covers" in order to connect to the database for interactive debugging. In this case, the user does not issue the SQLPREP or SQLBIND commands and so cannot specify the DATE format to be used using the /F=format option. However, there is a MicroFocus add-on product which the customer can purchase called Host Compatibility Option (HCO). This makes DB2/2 as compatible as possible with the host DB2 product. (The host DB2 product allows the user to specify the format of the DATE required). One of the options supported by HCO permits the user to specify the DATE format required by the MicroFocus COBOL program that is being tested. This in turn makes it easier for a customer to develop and debug applications within an OS/2 DB2/2 environment that will be run in a production environment on a host DB2/2 environment. 40 DB2/2 And DB2/6000 Questions and Answers 8. PROBLEM / QUESTION How do I click on the OK button if I'm installing DB2/2 without a mouse? SOLUTION / ANSWER Press Alt-Up Arrow to get to the notebook tabs, then use the tab key to select OK and press Enter. 9. PROBLEM / QUESTION I'm getting SYS1041 when I attempt to build the sample database using the SQLSAMPL program. SOLUTION / ANSWER You may have installed DB2/2 as a client only with no database support. This means that no databases may reside on this machine. Type DBINST, select REINSTALL, and specify Client With Local Database support. Load the diskettes as requested, and reboot when the reinstallation procedure is completed. 10. PROBLEM / QUESTION Which packages of DB2/2 can store databases on a machine? SOLUTION / ANSWER Both DB2/2 multi-user and single-user versions can create databases. For the single-user version, configure it as a client with local databases. For the multi-user version, configure it as a server. 11. PROBLEM / QUESTION Does DB2/2 allow the use of extended SBCS characters in the userids to access local DB2/2 databases or host databases through DDCS/2? SOLUTION / ANSWER No. This restriction is documented in "Appendix B: DB2/2 Naming Rules" on p.209 of Database 2 OS/2 Guide (S62G-3663). 12. PROBLEM / QUESTION How do I create a SYSADM user from UPM? DB2/2 and DDCS/2 41 SOLUTION / ANSWER Any ADMINISTRATOR or LOCAL ADMINISTRATOR created through UPM by default becomes a SYSADM to DB2/2. 13. PROBLEM / QUESTION How do I get a report set up with headers at the break points? SOLUTION / ANSWER Set breaks with headers in the REPORT details within QM. 14. PROBLEM / QUESTION Is there a utility for recreating the DB2/2 icons? SOLUTION / ANSWER No. There is a fax that explains how to set it up again if they need to be recreated. 15. PROBLEM / QUESTION How do I use the forcein parameter for filetmod on import? SOLUTION / ANSWER If you are using SQLDBS do the import then filetmod must be a host variable. Filetmod does not need to be a host variable if you are using the DBM command line processor. 16. PROBLEM / QUESTION I'm getting SQL01042C during STARTDBM. SOLUTION / ANSWER The error may be caused by a corruptted NET.ACC file. This file is accessed during STARTDBM. To resolve this you can copy the NET.ACC file from diskette #1 of the DB2/2 installation diskettes. Following this, you need to catalog the database before the database can be accessed. Note: You will lose the userids that you have created on this machine as the NET.ACC file contains this data. 17. PROBLEM / QUESTION 42 DB2/2 And DB2/6000 Questions and Answers I'm getting 'log full' errors (SQL3186) when I issue a large number of transactions. SOLUTION / ANSWER You must either issue smaller numbers of transactions followed by commits to free up log space or allocate more log space in the DB configuration. 18. PROBLEM / QUESTION I'm getting SQL0551 while performing an SQL command and I don't have the required authority to perform the requested operation. SOLUTION / ANSWER Try performing an SQLBIND @SQLUBIND.LST . This has resolved the problem on multiple occasions although this is not the only cause. You should always check the authority level of the user against the request they are trying to perform to ensure it is OK. 19. PROBLEM / QUESTION The FFST log contains a number of Database Manager entries originating from SQLO with a probe point of x'0A'. Is this a problem? SOLUTION / ANSWER Not necessarily. FFST log entries with this originator and probe point are generated whenever a negative SQLCODE is placed into an SQLCA. Get the hexadecimal value for the SQLCODE from the SQLCA logged in the SQLCA, convert it to a negative decimal number, and look up the corresponding SQL error message in the DB2/2 Messages and Problem Determination Guide to determine the actual error. 20. PROBLEM / QUESTION Is an integer greater than 10 significant digits treated as a decimal? SOLUTION / ANSWER Yes. Any number with greater than 10 significant digits is treated as a decimal within EE 1.3, ES 1.0, and DB2/2. 21. PROBLEM / QUESTION DB2/2 and DDCS/2 43 Receiving SQL1042C on STARTDBM. SOLUTION / ANSWER The system date was incorrect. Rather than replace the NET.ACC file the ZEROMODL tool can be used to correct 50 bytes in NET.ACC file. The next STARTDBM will reinitialize the system date in the NET.ACC file. 22. PROBLEM / QUESTION Unable to print to a PostScript printer. SOLUTION / ANSWER This is not supported. You must use a different driver (that is not PostScript). 23. PROBLEM / QUESTION Is it possible to directly update any of the columns in a SYSIBM table? SOLUTION / ANSWER No. No one can update, delete or insert records into these tables except SYSIBM (DB2/2 itself). 24. PROBLEM / QUESTION Experiencing problems cataloging new databases or nodes. SOLUTION / ANSWER This could be caused by corruption in the directory information. If you do not mind erasing what you have already cataloged, you can delete the \sqldbdir or \sqlnodir directories whenever you want. On the first attempt to use them, they will be recreated (obviously empty). The database and node directory formats are also inconsistent between DB2/2 and CAE/* (or SDK/*). When the Client Application Enabler (CAE) products and the Software Developer's Kit (SDK) products were built, a new format was used. 25. PROBLEM / QUESTION Is there any way to restore the database if all I have is a backup of the \SQLnnnnn directory? 44 DB2/2 And DB2/6000 Questions and Answers SOLUTION / ANSWER Yes, although problems can occur and this is not supported. First, create a new database with the same name. Copy your copy of the database into the directory just created. This should work. In future, use the proper backup and restore utility included with the product. This is the only supported method. 26. PROBLEM / QUESTION If DB2/2 has performed x hours of log file processing (for inserts, updates or deletes) and there is a COMMIT failure, how long will the ROLLBACK take to complete? SOLUTION / ANSWER Essentially, you have to redo all of the transactions, so the time it will take should be approximately the same. 27. PROBLEM / QUESTION Receiving an SQL0805N when attempting to run a window application against my DB2/2 database. The Windows application can be from an OS/2 machine or a native DOS/Windows machine. SOLUTION / ANSWER This is caused because either your application se or the database utilities are not bound to the database. Either bind your application or execute a bind against @db2ubind.lst, @ddcsbind.lst or @sqlubind.lst as appropriate for your installation. 28. PROBLEM / QUESTION How can I move a big database from one machine to another? SOLUTION / ANSWER a. Turn copy protect off in your database configuration. b. BACKUP the database to a LAN drive or a tape. c. RESTORE the database to the new machine using the CREATE NEW option. DB2/2 and DDCS/2 45 29. PROBLEM / QUESTION Receiving an SQL1042C when doing a STARTDBM. SOLUTION / ANSWER There are many causes. One possible cause is an incorrect system date/time; another is database corruption of some kind. 30. PROBLEM / QUESTION When printing from QM, the extra blank pages and pages with only a control character were printed with the report. SOLUTION / ANSWER Properly define a printer nickname and a profile with the nickname for the database. Be sure to activate the profile. 31. PROBLEM / QUESTION How can I change the workstation name that I set during the installation of DB2/2? SOLUTION / ANSWER This name is the NNAME setting in the database manager configuration. You can change this with the command: UPDATE DATABASE MANAGER CONFIGURATION USING NNAME . 32. PROBLEM / QUESTION Can a DB2/2 standalone machine act as a client and connect to remote databases? SOLUTION / ANSWER You can install DB2/2 as a standalone, or as a client with standalone databases. The latter will allow you to connect to remote databases. 33. PROBLEM / QUESTION How many times should a server and a client beep when they have enough NETBIOS resources? SOLUTION / ANSWER 3 times and once respectively. 46 DB2/2 And DB2/6000 Questions and Answers DB2/6000 AND DDCS/6000 ______________________ COMMUNICATIONS & DDCS _____________________ 1. PROBLEM / QUESTION When I connect to a SQL/DS machine I'm getting SQL30080 0010-00000000 with sense data 08640001. SOLUTION / ANSWER The SQL/DS server machine was not brought up with the correct parameter to allow DRDA protocol. When bringing up the SQL/DS machine, use: SQLSTART DB(dbname) parm(protocol=AUTO) OR SQLSTART DB(dbname) parm(protocol=DRDA) 2. PROBLEM / QUESTION I can't get DBM DROP DATABASE for a host database to work. SOLUTION / ANSWER If you want to execute a DROP DATABASE against any host database, you must write an application (in C or another language) to prepare the statement, and then execute it. 3. PROBLEM / QUESTION My client gets SQL30080 with sense data 081C0103. SOLUTION / ANSWER This is usually caused by having an incorrect destination address in the LINK configuration of the client. Check this and if necessary, change it to the correct one. 4. PROBLEM / QUESTION Only one user can connect to DB2/MVS. DB2/6000 and DDCS/6000 47 SOLUTION / ANSWER This is usually caused by missing Mod table definitions on VTAM, so define the correct Mod tables on VTAM and try again This can also be caused by MAXREMOTE=1 on the DSNTIPE panel of DB2 DDF install or MAXSESSIONS=1. Increase one or both of these as needed. 5. PROBLEM / QUESTION Can you use SELECT statements to select different members of an AS/400 physical file (table)? SOLUTION / ANSWER No, even SQL/400 cannot do that. Only the first member can be selected. (There may be some techniques on AS/400 where you can fool the system by using alias name for each member but, we are not aware of any). 6. PROBLEM / QUESTION My DDCS connection hangs in VTAM with PCINIT status. SOLUTION / ANSWER This may be caused by the wrong format in LOGMODE in VTAM. The continuation line must start on column 16. 7. PROBLEM / QUESTION I'm getting SQL30080 Reason Code 0A while trying to issue a connect from a database client to the database server. SOLUTION / ANSWER The number of the remote connections may have exceeded the configured limit. 8. PROBLEM / QUESTION DOS and Windows Clients userids cannot have the pound sign (#). DB2MVS userids have #. How can I workaround this problem? SOLUTION / ANSWER In DB2/MVS, you can use USERID TRANSLATION FUNCTION to "translate" userids without the pound sign to 48 DB2/2 And DB2/6000 Questions and Answers other userids with pound sign. This workaround has to be done on MVS. 9. PROBLEM / QUESTION I'm getting 0003-00000004 or 0003-00000005 APPC errors on the client while connecting to a DB2/6000 Server. SOLUTION / ANSWER Both clients may be using the same Local LU (eg. LU1) Ensure that all clients have a unique Local LU. 10. PROBLEM / QUESTION I'm getting SQL30080 14 error. SOLUTION / ANSWER This may be caused by incorrectly cataloging the workstation on the client. 11. PROBLEM / QUESTION I'm getting SQL30061N when connecting to AS/400. SOLUTION / ANSWER On AS/400 use the command ADDRDBDIRE to add a local database directory entry and use the name for TARGET DATABASE in DCS directory. 12. PROBLEM / QUESTION DBM EXPORT hangs or returns SQL1325N when I issue it against a host database. SOLUTION / ANSWER EXPORT hangs because it tries to perform a DBM CONNECT. If you use dependent LU you already have a connection. The second connection will hang. 13. PROBLEM / QUESTION Poor performance occurring with the DOS Client (ES 1.0 , DB2/2, CAE/DOS or SDK/DOS) when using Lan Support Program Version 1.35 and the device driver DXMT0MOD.SYS. SOLUTION / ANSWER DB2/6000 and DDCS/6000 49 Add the parameter PBA=0 to the device statement in the CONFIG.SYS on the DOS Client. It should look something like this: DEVICE=X:\LSP\DXMT0MOD.SYS PBA=0 ... 14. PROBLEM / QUESTION Having difficulty with connecting CAE/2 to DB2/6000. Receiving an SQL30081 error reason code 24 using CPI-C. This occurs during the connect. SOLUTION / ANSWER You must use the CP name of the OS/2 machine in the REMOTE LU name field of the Logical Connection Profile. To get this look at the FQ_CP_NAME under DEFINE_LOCAL_CP in your NDF file. You can use other defined Local LU names only if you set the environment variable APPCLLU= where this name matches up with the REMOTE LU name field of the Logical Connection Profile. 15. PROBLEM / QUESTION SQL0552N when binding to MVS database SOLUTION / ANSWER If AUTHID on MVS is set to blank this will cause your userid to be translated to an ID that has no MVS authority. Change the AUTHID to something else to allow your authorities to be used. INSTALL AND MIGRATION _____________________ 1. PROBLEM / QUESTION If I am trying to install DB2/6000 or DDCS/6000, what does it mean when a message appears stating that sna.sna.obj does not exist? SOLUTION / ANSWER If you receive this, you have either selected the cs_sna option in the DB2/6000 installation, or the prerequisite option on the DDCS/6000 option. In both cases, there is a check to ensure that you have the correct version of SNA/6000 installed. This message appears when SNA/6000 is not installed, the wrong version is installed, or there is an installation problem. The solution is to install or reinstall SNA/6000. 50 DB2/2 And DB2/6000 Questions and Answers APPLICATION DEVELOPMENT _______________________ 1. PROBLEM / QUESTION I'm getting a SQLCODE of 0 when I expect a SQL0100. SOLUTION / ANSWER You are getting the 0 because you may be closing the cursor before checking for EOF, thus the close cursor was returning the 0 in the SQLCODE. 2. PROBLEM / QUESTION Why does DB2/6000 require so much extra log space when a large unit of work is being performed? SOLUTION / ANSWER Reserved space is allocated for a potential ROLLBACK of a unit of work. This reserved space is logical space that is not used until the ROLLBACK operation would occur. Therefore, a SQL0964 error (Log full) will occur unless the required space is available. 3. PROBLEM / QUESTION Why am I getting SQL0501 errors occurring? SOLUTION / ANSWER This error is being caused by attempting to use a cursor after it has been closed or attempting to close a cursor following a commit. 4. PROBLEM / QUESTION How do I do a backup from a remote machine? SOLUTION / ANSWER You need to use a DARI program. (see the DB2/6000 Programming Guide and Reference for more detail) 5. PROBLEM / QUESTION How many table/view references are allowed in a single SQL statement? SOLUTION / ANSWER The total number of tables and/or views that may be referenced in a single SQL statement is 15. DB2/6000 and DDCS/6000 51 6. PROBLEM / QUESTION Can a user update long varchar data using the command line processor? SOLUTION / ANSWER No. Long varchar data can only be updated to the database via a C program. 7. PROBLEM / QUESTION Why does the DB2 precompiler reject host variables of length greater than 254 when using FORTRAN? SOLUTION / ANSWER This is due to the inability of FORTRAN to handle structure type variables and variable length variables. Workarounds include: 1. Use C or COBOL 2. Use the SQLDA structure in FORTRAN. MISCELLANEOUS TOPICS ____________________ 1. PROBLEM / QUESTION I'm getting SQL0101 error. SOLUTION / ANSWER This is usually caused by an insufficient statement heap size in the database configuration. Increase the statement heap size from 64 to 255 (max). The default is 64 in DB2/2 (for server) as it was in ES 1.0. A query that runs in ES 1.0 may not work in DB2/2 even if the statement heap size is the same as DB2/2 uses extra memory. The SQL0101 may also be caused by referential constraints limitations in cases of UPDATEs. 2. PROBLEM / QUESTION I need information on the DB2/6000 call level interface. SOLUTION / ANSWER This CLI is a callable SQL programming interface for the C language. It is basically an applications program interface for db access which uses function 52 DB2/2 And DB2/6000 Questions and Answers calls to invoke dynamic SQL statements. It is an alternative to embedded dynamic SQL. 3. PROBLEM / QUESTION I'm getting SQL0551 while performing an SQL command. SOLUTION / ANSWER Try performing an SQLBIND @SQLUBIND.LST . This has resolved the problem on multiple occasions although this is not the only cause. You should always check the authority level of the user against the request they are trying to perform to ensure it is OK. 4. PROBLEM / QUESTION Is an integer with greater than 10 significant digits treated as a decimal? SOLUTION / ANSWER Yes. Any number with greater than 10 significant digits is treated as a decimal within EE 1.3, ES 1.0, and DB2/2. 5. PROBLEM / QUESTION Is REXX supported with DB2/6000? SOLUTION / ANSWER No. 6. PROBLEM / QUESTION How many shared segments does DB2/6000 get? SOLUTION / ANSWER It gets 2 shared segments as soon as a db2start is issued, one for communication and one for the database engine. After this it gets one segment for every database in use, and another one if TRACE has been started. Therefore, the number of shared segments = 2+(# db in us e)+(1 if trace is running) DB2/6000 and DDCS/6000 53 54 DB2/2 And DB2/6000 Questions and Answers FEEDBACK QUESTIONNAIRE ______________________ The workstation database service group is interested in what your opinions are regarding this document. We would appreciate your taking a moment to print out this page and respond to the questions below. For each question, enter a number from 1 to 5 where: 1 = Very satisfied 2 = Satisfied 3 = Neither satisfied or dissatisfied 4 = Dissatisfied 5 = Very dissatisfied How satisfied are you with .... 1. the technical accuracy of this document ? _______ 2. the type of information provided in this document ? _______ 3. the quantity of information provided in this document ? _______ 4. the layout of this document ? _______ 5. the ease of finding specific topics in this document ? _______ 6. the searching interface provided with this document ? _______ Additional Comments: please feel free to add any additional comments. Feedback Questionnaire 55 When complete, please fax this form to 416-448-2783. If you are willing to be contacted about your comments please provide your name and phone number. 56 DB2/2 And DB2/6000 Questions and Answers IBM Printed in U.S.A.