Conference heron::dw_tools

Sorry folks,
I though the setup documentation was in the directory or .EXE file, but clearly
it isn't. What's more I don't find the electronic copy of my printed version.
I'll get another copy and post the reply here when it's available.

I've received the install guide again (revised), it's now in

heron::pub_dsk:[public.datamart_service.bobvideo]overview_installation.doc

There'll be a new CD version of the demo soon, as soon as I get it I'll post it
here.

	Well, we finally got the Bobsvideo demo to work and had it running
	at a defence road show last week. But we had a lot of problems. 

	The ODBC trace facility proved to be the most useful method of 
	finding out what was going on - and I recommend using it if you
	encounter problems. The NT Event viewer also provided some pointers.

	One thing still amazes me is the inability of client/sever
	based software (such as Informatica) to provide meaningful error 
	messages or information. For the problems I encountered the 
	information was there. I also thought the online documentation when 
	it came to troubleshooting was not good.

	Anyway, the following information will hopefully make subsequent 
	installations less painful than my own. The list of problems 
	encountered and solutions would have been far greater but a lot of 
	them are covered in the installation guide. I appreciate that some of
	the fixes are not very elegant but they work. 

	Nick.


			----------------------------
	Problem:

	Informatica (Intersolve) ODBC driver not licensed for Business 
	Objects.

	Having set up the ODBC driver for use by Business Objects an
	information message is returned when BO goes to use the driver 
	regarding product licensing. 

	Solution:

	Use the SQL Server ODBC driver. Use the Intersolve driver for 
	Informatica and SQL Server driver for BO.

			----------------------------
	Problem:

	The Intersolve driver does not permit the use of the keyword 'NULL'.
	(Can you believe that?).

	When creating a new repository the table create statements will fail 
	stating that the Intersolve ODBC driver will not allow the use of the 
	keyword 'NULL'. 

	Solution:

	Set up a SQL Server ODBC driver to to create the repository and then
	switch back to the Intersolve driver for future use. (Then again 
    	despite the recommendations why	bother to switch back?).

			----------------------------
	Problem:

	Repository versions.

	Having installed and configured Informatica and created a new 
	repository, when switching to use the Bobsvideo repository for the 
	first time an error is returned by Informatica stating 'cannot handle 
	this version of the Repository'. 

	Solution:

	Change the value of REPVERSION in table OPB_REPOSIT to match the 
	same value (version) as the working repository in both DemoMetaData
	and DemoMetaData2. 

	(update OPB_REPOSIT set REPVERSION = 30 where RECID = 1)

	In the absence of any meaningful error messages or explanations 
	this was the only option (although I would not normally advocate 
	changing system information in this manner).

			----------------------------
	Problem:

	Informatica subject areas remain locked although not in use.

	When trying to access a subject area an information message is 
	returned stating that the subject area is locked by another user.
	Using the Repository Manager to unlock the areas, restarting the
	services and rebooting will not release the lock.

	Solution:

	The problem usually occurs after a system failure. Informatica uses
	lock flags (1 = locked) in system tables to manage subject area
	locking. When a failure occurs Informatica does not reset this value
	to release the locks so the subject area remains locked. 

	Reset the lock flag INUSE to 0 in the table OPB_SUBJ_AREA for the
	subject area that has been locked.

	(update OPB_SUBJ_AREA set INUSE = 0 where SUBJECT_AREA = 'name')

	Check the column name 'SUBJECT_AREA' - it may not be correct and I
	can't check it at the moment. Once again this is changing the system
	information but in the absence of any other solution it does work.
	I would say that this is a bug in the software. If Informatica 
	chooses to use 'lock flags' then it should also guarantee that it
	can handle system failures correctly.

			----------------------------	
	Problem: 

	PowerMart Server Manager session execution fails to load data
	during demo.

	The session 'S_Sales_Facts_Update' will not execute and returns an
	error and fails to load the data to the target database.

	Solution:

	The user demosa does not have write permission on the table 
	SALESUPDATE. This is due to an error in the Bobsvideo SQL create 
	script (user permissions).

	The Bobsvideo SQL script returns an error when trying to set up
	an alias for demosa (dbo). The error is 'demosa is already mapped
	to user demo'. This prevents the data source to target part of the
	demo from being executed

	Grant all permissions to group public in DemoMart database.

	
    

Some comments:
	- don't use the Intersolv drivers for ANY component. Use the
          standard SQL Server drivers.

	- Infomratica repository manager can unlock the subject are through
          the 'unlock subject area' (subject area pull-down menu).

Glad you got it all running finally!

next test will be the shortly to be released Telecom demo...!!!

    
    Rob,
    
	Some comments on your comments...
    
	>> don't use the Intersolv drivers for ANY component. Use the
        >> standard SQL Server drivers.
    
    	The Digital Data Mart documentation recommends that you use the
    	Intersolve drivers when using Informatica. Is there a reason (other
    	than it doesn't work) for not using the Intersolve drivers?

	>> Infomratica repository manager can unlock the subject are through
        >> the 'unlock subject area' (subject area pull-down menu).

    	No, I'm afraid it doesn't. It says it does but when you go to
    	access the subject area it is still locked. This is backed up by
    	looking at the values in the system table as I described in -.2.
    	My view is that it is a bug in the Informatica sofatware. The work
    	around does the trick though.
    
	
	>> next test will be the shortly to be released Telecom demo...!!!
    
    	I can't wait!
    
    	Nick.
    

	Re: .9 

	I made a few mistakes when writing up my notes on the
	problems with BobsVideo.

	- the load failure problem is on the SALES_FACTS table
	  not the SALESUPDATE table (which does not exist).

	- the tables and columns referenced in the locked subject 
	  area problem were incorrect. The table name is OPB_SUBJECT
	  and the SQL should have been:

	update OPB_SUBJECT set INUSE = 0 where SUBJ_NAME = 'name'

	My apologies. Hope it didn't cause any problems. I have
	added a couple of scripts that can be run to fix the
	repository version and locked subject area problems as
	recompense. 

	Nick.

	The following 2 scripts were setup following problems with the 
	BobsVideo repositories DemoMetaData and DemoMetaData2.

/******************************************************************************/
/*                                                                            */
/* Name: Version Change Script - Informatica Repository Version               */
/* 									      */
/* Created By: Nick Clark						      */
/*                                                                            */
/* Date: 18th February 1997                                                   */
/* 									      */
/* Ammendment Record							      */
/*                                                                            */
/* Description:								      */
/*                                                                            */
/*	 Script to reset the Repository version in the Informatica MetaData.  */
/*	 Problem is encountered with PowerMart V3.0 when executing repository */
/*       connection for the first time.					      */
/*                                                                            */
/*       The script has been configured for MetaData stored for the Bob's     */
/*       Video Digital Data Mart Demo but could be ammended to be executed    */
/*       for other database respositories if required.			      */
/*                                                                            */
/*       Warning: This is a temporary workaround in the absence of            */
/*	          alternative solutions and error explanations.               */
/*				                                              */
/******************************************************************************/

SET nocount on

USE DemoMetaData
GO

DECLARE @dttm varchar(55)
SELECT @dttm=convert(varchar,getdate(),113)
RAISERROR('Executing Repository Version update at %s ....',1,1,@dttm) with nowait
GO

RAISERROR('Updating DemoMetaData database ....',1,1) with nowait
GO

update OPB_REPOSIT set REPVERSION = 30 where RECID = 1
GO

USE DemoMetaData2
GO

RAISERROR('Updating DemoMetaData2 database ....',1,1) with nowait
GO

update OPB_REPOSIT set REPVERSION = 30 where RECID = 1
GO

/******************************************************************************/
/*    Verify Updates successful         */   


RAISERROR('Verify DemoMetaData database ....',1,1) with nowait
GO

select RECID, REPVERSION from OPB_REPOSIT
GO

USE DemoMetaData2
GO

RAISERROR('Verify DemoMetaData2 database ....',1,1) with nowait
GO

select RECID, REPVERSION from OPB_REPOSIT
GO

RAISERROR('Completed Respository Version updates for DemoMetaData and DemoMetaData2 Load',1,1) with nowait
GO

/******************************************************************************/
/*    End Script         */


DECLARE @dttm varchar(55)
SELECT @dttm=convert(varchar,getdate(),113)
RAISERROR('Completed Repository Version Update Script at %s.',1,1,@dttm) with nowait
GO

/******************************************************************************/
/*                                                                            */
/* Name: Unlock Script - Informatica Locked Subject Area                      */
/* 									      */
/* Created By: Nick Clark						      */
/*                                                                            */
/* Date: 18th February 1997                                                   */
/* 									      */
/* Description:								      */
/*                                                                            */
/*	 Script to unlock a subject area when the area has become permanently */
/*	 locked. This error usually occurs after a system failure and is      */
/*       related to PowerMart V3.0.					      */
/*                                                                            */
/*       The script has been configured to unlock all locked subject areas    */
/*       - individual areas cannot be selected. It works by resetting the     */
/*       the lock flags in the system table (opb_subject).                    */
/*                                                                            */
/*                                                                            */
/*       Warning: This is a temporary workaround in the absence of            */
/*	          alternative solutions and error explanations.               */
/*                This script should only be used if the Informatica          */
/*                Repository Manager cannot unlock the area.                  */ 
/******************************************************************************/

SET nocount on

DECLARE @dttm varchar(55)
SELECT @dttm=convert(varchar,getdate(),113)
RAISERROR('Executing Subject Area unlock script at %s ....',1,1,@dttm) with nowait
GO

/******************************************************************************/
/*    DemoMetaData         */

RAISERROR('Unlocking DemoMetaData database ....',1,1) with nowait
GO

USE DemoMetaData
GO

DECLARE @subject_area char(50)
start_update:
  if (select count(*) from opb_subject where inuse = 1) > 0
	goto unlock_area
  else goto end_update
    unlock_area:
      set rowcount 1
      select @subject_area=subj_name from opb_subject where inuse = 1
	RAISERROR ('Subject Area %s has been reset.',1,1,@subject_area) with nowait
	update OPB_SUBJECT set INUSE = 0 where SUBJ_NAME = @subject_area
      goto start_update
end_update:

RAISERROR('DemoMetaData Subject Areas have been unlocked',1,1) with nowait
GO


/******************************************************************************/
/*    DemoMetaData2         */

RAISERROR('Unlocking DemoMetaData2 database ....',1,1) with nowait
GO

USE DemoMetaData2
GO

DECLARE @subject_area char(50)
start_update:
  if (select count(*) from opb_subject where inuse = 1) > 0
	goto unlock_area
  else goto end_update
    unlock_area:
      set rowcount 1
      select @subject_area=subj_name from opb_subject where inuse = 1
	RAISERROR ('Subject Area %s has been reset.',1,1,@subject_area) with nowait
      	update OPB_SUBJECT set INUSE = 0 where SUBJ_NAME = @subject_area
      goto start_update
end_update:

RAISERROR('DemoMetaData2 Subject Areas have been unlocked',1,1) with nowait
GO


/******************************************************************************/
/*    End Script         */


DECLARE @dttm varchar(55)
SELECT @dttm=convert(varchar,getdate(),113)
RAISERROR('Completed Subject Area Unlock Script at %s.',1,1,@dttm) with nowait
GO

    

    
>    	The Digital Data Mart documentation recommends that you use the
>    	Intersolve drivers when using Informatica. Is there a reason (other
>    	than it doesn't work) for not using the Intersolve drivers?

	The documentation in question needs to be reviewed (my job). If you
	use the Intersolv drivers from a tool other than Informatica (eg:
	Business Objects) then a load of warning messages are written
	to the screen - very annoying, and absolutely unusable
	in a customer situation.

	Thanks for your comments -