0

I'm trying to execute the below sql file using db2 in the command line. But Im getting the below error. I'm not sure where it went wrong.

"DB21007E End of file reached while reading the command"

I'm executing the below sql file using db2 -tvf sqlfile

connect to ****** user ***** using ******
export to "D:\Vikas.csv" OF DEL MESSAGES
select
T1.ROW_NUM,
T5.DETAIL_TYPE_CD,
T1.ADMIN_FEES_TICKET,
T1.ADMINISTRATIVE_FEES,
T1.BASE_RENT,
T1.CITATIONS,
T1.COLLECTION_REPO_FEES,
T1.DESC,
T1.EFFECTIVE_DATE,
T1.LATE_CHARGE,
T1.MISC_FEE,
T2.STATUS_CD,
T4.ROW_ID,
T3.ROW_ID,
T2.BUILD,
T1.REVERSE_FLG,
T1.NSF_FLG,
T2.PR_CON_ID,
T1.PROC_DATE,
T1.PROPERTY_TAX,
T1.REGISTRATION_FEES,
T1.REPAIR_FEES,
T1.SALES_TAX,
T1.TERMINATION_FEES,
T1.TOTAL_TRANS,
T1.TRANSACTION_TYPE

FROM
SIEBEL.LSE_INPHIST_VIEW T1
LEFT OUTER JOIN SIEBEL.S_ASSET T2 ON T1.ACCOUNT_NUM = T2.ASSET_NUM
LEFT OUTER JOIN SIEBEL.S_ASSET_CON T3 ON T2.ROW_ID = T3.ASSET_ID AND
T3.RELATION_TYPE_CD = 'Obligor'
LEFT OUTER JOIN SIEBEL.S_ASSETCON_ADDR T4 ON T3.ROW_ID = T4.ASSET_CON_ID 
AND T4.USE_TYPE_CD = 
'Bill To'
LEFT OUTER JOIN SIEBEL.S_PROD_INT T5 ON T2.PROD_ID = T5.ROW_ID
WHERE
(T1.ACNT_ID = '01003501435')
ORDER BY
T1.ACNT_ID DESC,T1.PROC_DATE DESC WITH UR

Now I'm able to connect the DB2 but while executing the select statement it's throwing the below error

SQL0104N  An unexpected token "T1.ROW_NUM" was found following "SELECT".
Expected tokens may include:  "SELECT".  SQLSTATE=42601
Ravi Kumar
  • 11
  • 5
  • Are both of the statements in the SQL-file terminated with a semi-colon (or other configured delimiter) ? Are you using the Db2 CLP to run the file at the operating-system shell (`db2 -tvf sqlfile` ) ? Edit your question to give more clarity, do not use comments – mao Jun 17 '21 at 06:55
  • You have fixed the original problem (DB21007E) presumably by adding a statement delimiter to each statement in the file as suggested by first commend above. Now you have a different symptom. You should not use one question for two different unrelated symptoms. Instead, for each symptom , ask a different question. That's how stackoverflow works. – mao Jun 17 '21 at 07:50
  • Your `export` statement may be invalid, because it shows `MESSAGES` without the required filename to contain the messages. Lookup the Db2 documentation for EXPORT command https://www.ibm.com/docs/en/db2/11.5?topic=commands-export – mao Jun 17 '21 at 07:57
  • Thanks mao. I'm able to fix it and file is generating now. I have raised a different question for the missing of headers in the CSV file. – Ravi Kumar Jun 17 '21 at 08:11

1 Answers1

0

The original question text had symptom ""DB21007E End of file reached while reading the command"".

This was caused by failure to terminate each statement with a delimiter. The default delimiter for SQL-statements is a semi-colon (;) . You are free to use a different delimiter, in which case you must give extra configuration information either on the command line or inside the sqlfile itself to specify the new delimiter.

Each statement in the sqlfile that you submit with db2 -tvf sqlfile with the Db2 command line processor must be valid. If any single statement has incorrect syntax, then by default Db2 will return an error and continue to execute the next statement in the file. If you want Db2 to stop execution at the first error and quit the sqlfile, then use the +s command line option.

For any other symptom , different from DB21007E End of file reached while reading the command, you should ask a separate new different question. Always verify your queries independently before putting them into a scriptfile, for example, run the SELECT... in your favourite GUI tool and fix the mistakes before putting the working query into the SQL file for scripting. For the export command you are using the MESSAGES option but you have forgotten to specify the filename to contain those messages. Refer to the documentation.

mao
  • 11,321
  • 2
  • 13
  • 29