Here’s a new feature for DBsandBI.com – a weekly update of what I’m working on.
1. All client/server connections, for BI, to our AS400 via ODBC needs to be set to read only and have the commit mode set to Commit immediate (*NONE) instead of the default Read Uncommitted (*CHG)
- This was causing locks on table and a batch process wasn’t completing
- Error handling in JDE doesn’t handle it correctly and doesn’t give an error
- IBM stated that these should act the same for select statements, but not the case with our environment as we could reproduce the error in a controlled environment
2. New custom web app and SQL server not talking well in QA environment
- On some inserts to the database, we were getting:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
- The web page was saying:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)
- As it turns out, the comments field were to large, field sizes were adjusted on the web page and everything worked fine.
3. ETL Process using SQL 2000 DTS packages failed
- Nothing changed
- Flaky error, couldn’t get consistent results
- There was a RTRIM(LTRIM()) call in the source that was looking at AS400. I removed it and everything worked.
- As an immediate fix, I exported data to excel and replace the step in the ETL with that.
4. Cognos Data Manager demonstration
- Lunch demonstration/informal Q+A of Cognos Data Manager
- Presenter has years of experience
5. DTS Moving script – A script to use in a DTS package on SQL 2000 to move a DTS Package or Packages from one server to another.
6. SQL Versions Table – a page I created as a quick reference to find what version of SQL is running
Coming Up Next Week:
1. Custom web app to production
2. Quarter End
3. Report of all Cognos8 Reports
4. Disaster recovery test – SQL 2000 server