Oracle Business Intelligence (BI) Publisher utilizes a Quartz, an open-source job-scheduling system. To setup the BI publisher scheduler you are required to install the scheduler tables to a database option of your choice IBM DB2, Microsoft SQL v2000 or v2005, Sysbase Adaptive Server Enterprise, or Oracle v9i, Oracle 10g, Oracle 11g.

In my scenario, we were using IBM DB2 via z/OS platform. When the ‘install schema’ task was attempted we identified an issue and became unsuccessful installing the job scheduler schema tables.

According to the application error : the Schema installation failed while creating tables. Schema may already exist. Please remove the existing schema or choose another database and try again.

According to the DB2 Database Errors : the installation script is trying to use the column data type of BIGINT which is NOT supported for DB2 on a z/OS platform. As I understand it from our Database Analyst, BIGINT will ONLY work on DB2 UDB. The documentation recommends that the IBM DB2 v8.1 & v9.1 are supported and recommended. However there is no specific designation on DB Platforms support documentation. 

In our case we had to change our platform option to Oracle because we did NOT have DB2 on UDB (only on z/OS)

Oracle Support confirms the issues we experienced and why – this is their response :

See the Certification Guide:
http://download.oracle.com/docs/cd/E12844_01/doc/bip.1013/e12692.pdf
We only support DB2 UDB:
These are the only two DB2 versions supported for scheduler:
IBM DB2 for UDB EE 8.2, 9.5

Hope this helps others who are experiencing the same issue.

Thank.You – Josef

REM Start of Script

@echo off
REM – File: [File Name]
REM – Description: [Description]
REM – Author: [You]
REM – ADD REM comments if you don’t want to restart any
REM – of Services
echo Restarting Services…
echo ======================================================
net stop “[Service Name]”
net start “[Service Name]”
echo ======================================================
echo Services Restarted

PAUSE

REM End of Script

NOTE :You can add above script into a *.bat file and then run this adhoc or using Windows Scheduled Tasks

A helpful tip for resetting your Identity Seed Column Property. . .

Open Table > Design Mode > Select Column with Indentity Specification of ‘Yes’ and review the following settings:

  • (Is Identity) = Yes
  • Identity Increment = [user-defined value] (e.g. ‘1’)
  • Identity Seed = [user-defined] (e.g ‘1’)

Now for the actually Management Studio Statements. . . Open New Query Window :

  1. DBCC CHECKIDENT ([table name], NORESEED) — Will check identity information : current identity value ‘x’, current column value ‘1’.
  2. DBCC CHECKIDENT ([table name], RESEED, [numeric value]) — Will set the Identity Seed to [numeric value]
  3. Run Step # 1 to validate Step # 2 Change

If you’re scripting and looking to retrieve data from a relational or non-relational database this post should help. Whether you’re retrieving data from Microsoft or another database provider you will need the following API to interface with the database(s) : (A)ctiveX (D)ata (O)bjects

I put (2) examples together. . . 1st w/ Microsoft SQL Connection. . . 2nd w/ LDAP which will build on what you learn from the SQL example.

IMPORTANT – You are required to know connection credentials and be familiar with the data schema. If you don’t, consult with your database admin to gather these requirements.

Here is a VB Script example for connecting to a Microsoft SQL Server v2005 via ADODB API, and then retrieving the row output with a record set (rs) array. You can extend this in the user-defined sections (Highlighted in Blue)

Details of the following code :

  1. You will need access to the server’s file system via FSO (FileSystem Object). With FSO you can create new or access existing files. Do this by setting the FSO variable to the following.
  2. Declare the connection (con), command (com) and record set (rs) variables via DIM statement
  3. Set the connection variable (var)
  4. Set the command var
  5. Set the con properties : Provider. Connection String.
  6. Open the con
  7. Set com property to con : ActiveConnection
  8. Set com property with SQL query : CommandText
  9. Execute com and set results to rs array; cycle through records to rs array until end-of-file (EOF) is met; then end loop via Wend
  10. Close rs array

 VB Script for Microsoft SQL ADO Connection

Set fso = CreateObject(“Scripting.FileSystemObject”)
Dim con, com, rs

Set con = CreateObject(“ADODB.Connection”)
Set com = CreateObject(“ADODB.Command”)
con.Provider = “SQLOLEDB”
con.ConnectionString = “Driver={SQL Server};Server=[server name];Database=[database name];UID=[username];PWD=[password]
con.Open

Set com.ActiveConnection = con
   com.CommandText = “Select [field 1], . . . , [field n] from [table]”

Set rs = com.Execute

rs.MoveFirst

counter = 1

While NOT rs.EOF

[user defined logic]

rs.MoveNext

counter = counter + 1

Wend

rs.close

Once you have the data stored within the record set you can begin building your vbscript logic to determine what you want to do with the data stored within the record set (rs). This should get you rolling. . . If you have any questions about the above script or need help moving to the next step, let me know.

VB Script for Lightweight Directory Access Protocol Connection

Const LDAPSource = “LDAP://

Dim con, com, rs
    Set con = CreateObject(“ADODB.Connection”)
    Set com = CreateObject(“ADODB.Command”)
    con.Provider = “ADsDSOObject”
  con.Properties(“User ID”) = “[username]”
  con.Properties(“Password”) = “[password]
    con.Open

Set com.ActiveConnection = con
        com.CommandText = “Select [field 1], . . . , [field n] from ‘” & LDAPSource & “‘ where [condition(s)]

[ . .  record set logic . . ]

Don’t forget to close the record set logic . . .

I’m working with a situation that a production database was populating a notification table for a downstream process which would delete the data post-process. Here’s what would happen. . .Once a set of application rules inserted the formatted variables into respective fields a downstream process will retrieve the data, deliver notifications, and then re-query the database for that row and delete it.

So if you’re trying to retrieve the row(s) prior to the down stream process deleting it – you can use the following logic to continue looking until it finds data in the table. I did this to copy a production data row and then insert the results into a test/dev environment. This approach helped avoid impact of production processes to force data output.

NOTE : I initially built this without a timing mechanism so it would continually run until one or more rows returned – then a select statement follows to a specific set of fields from the table. To help matters, I introduced a delay in the loop, to avoid maxing out a CPU on the SQL Server, at a (5) second interval. . .

declare @count int
SET @count = 0

WHILE @count = 0
BEGIN
 set @count = (Select count(*) from [table name])
 waitfor delay ‘0:0:5’
END

Select * from [table name]

Thanx – J

If you’re an administrator of BMC Service Desk Express 9.x , you might find these SQL Statements helpful for retrieving the following SDE ticket types.

–INCIDENT Type

SELECT COUNT(*) FROM _SMDBA_._TELMASTE_

–PROBLEM Type

SELECT COUNT(*) FROM _SMDBA_._PROBMGMT_

–CHANGE Type

SELECT COUNT(*) FROM _SMDBA_._CHANGE_

–WORK ORDER Type

SELECT COUNT(*) FROM _SMDBA_._WORKORD_

Expand upon the statements as you need to introduce JOIN or WHERE Clauses. . . The above will get you started anyway. If you need help – just ask.

Thanx – J

 

If you want to maintain a healthy sharepoint environment, it takes requirements determined by the business and the system administrators. If you haven’t taken time to evaluate your user’s environment, start asking questions. Sharepoint will take planning.

Evaluate the business. Understand the users actions; how the environment works.  Physical and logical contraints need to be known. When you think about configuring the network, hardware, application, database, and disk storage layers of the environment. Define your sharepoint deployment strategy at the physical, process, logical, and site-level perspectives.

On a spectrum of Isolation to Scalability, consider the following:

  1. Physical Isolation : separation of networks for each site collection — not practical for most environments
  2. Physical Isolation : seperation of servers for each site collection — not practical for most environments
  3. Process Isolation : seperation of application pools for each site collection — Internet/Extranet, or Sensitive Data
  4. Logical Isolation : seperation of web applications for each site collection — Internet/Extranet, or Sensitive Data
  5. Logical Isolation : seperate host names for each site collection — Internet/Extranet, or Sensitive Data OR Internet (non-interactive/readonly) or Intranet
  6. Logical Isolation : seperate site collections for each site — Internet (non-interactive/readonly) or Intranet
  7. Sharepoint Site Isolation : multiple subsites, and workspaces in a site collection — Intranet Only
  8. Sharepoint Site Isolation : specific security for a list or library within a site collection or subsite — Intranet Only

Though measuring the isolation and scalability variables, think about how they determines your ability to administer this.

  • Will it be easy for you to limit the exposure of failures?
  • Will security be easy to manage?
  • If you’re introducing a highly-scalable environment you’re also inviting complexity — increasing risks to break.
  • If a user’s team loses their site, what will your degree of restoration be? Item level? Site Collection Level? Database Level?
  • If the architecture approach is #7 the site collections can grow (if you place no quota contraints) with many groups using sub-sites within a single site collection (a top-level site / logical partition within a contentdb). To restore a previous version of a particular sub-site may impact the all teams within this site collection, creating noise, possible data loss, etc. This is assuming you’re not using a technology like Microsoft DRM that will manage all this for you — however this solution requires large amounts of storage equating to more costs.
  • What about security management? Do you want to commit to managing item-level security? Do you want to manage multiple subsite permissions that are unique within a parent site collection? Wouldn’t it be easier to set the permissions at the site collection level and then require all subsite’s to inherit the parent-level perms?

So isolate the architecture as best you can, but keep it flexible to scale it to the degree your company intends to take the technology. Every business will have a different.

Easy. Right?

Tune in next for information on factors to consider when you’re putting data in Sharepoint.

  • How storage quotas will help. . .
  • Versioning is a convenience to user’s, right? What about additional storage factors?
  • What about disk inflation? Why is 1GB of RAW Fileshare Storage inflate to 1.2 GB of storage in Sharepoint?

I’ll have more on this topic, and it will certainly help plan for the needs of your business.

Sharepoint Favorites?

April 28, 2009

I’m constantly being hit with emails, phone calls, and IMs requesting a sharepoint url they have forgotten to thumb in their favorits. Ugh. . . Do I have time for this? Do you?

Sharepoint user’s adopting the web-share approach need a way to track down and it loox like Black Knight has the answer (http://www.theblackknightsings.com/)

You can download the solution from HERE

It addes a new menu item to the Welcome Menu (upper-righthand corner) called ‘Show All Sites’

It will then build a tree-view structure of all sites (YOU) has access to. If you have access to large number of sites, I hope you know the site by name because all the sites are listed by their given name hyperlinked to the URL.

Discussion in class today. . .

The following linx are the main search engine players on the internet today. When you think of other search engines like Lycos, Yahoos, and other sites (askjeeves.com) they aren’t actually using their own search algorithm, but instead feeding off Google or Bruceclay.com’s SEO environment. The exception is livesearch by Microsoft; by no surprise, going their own direction.

If you’re interested in training opportunities, read up on Bruce Clay’s website for analytic strategy, search optimization, etc.

Class Notes :

Linx

  • www.flex.org/showcase — Similiar to AJAX — (A)synchronous (J)ava (A)nd (X)ml | e.g. Google Search Refreshing the Search for field
  • http://www.microsoft.com/expression/ (Web Designer Products) — A step for designers to develop upon the data components driven by developer code

Recreational Thought

Rosetta Stone Perl-to-VB. . .

  1. Reduce Flat-File Evaluation WF to SQL-Based. . . (e.g. destination, configuration, logging mechanisms)
  2. How to manage events in high-volume?
  3. VB Driven Process, use SQL and/or Stored Procedures?

Visual Studio 2005 Tips:

 If you’re using the comment feature in Visual Studio

For Example :

// this is a comment

//TODO: mow the lawn

In the lower pane of the Visual Studio (the Task List pane) you can review the TODOs you have in your code. It will ONLY show your //TODO: comments, not all comments.

Thought I’d share

-J

 Definitions :

  • Class : blueprint
  • Object : Instance of a class
  • Field : placeholder in a class for data based on type
  • Property : Assessor to a field; defines what can set or get the data for a field
  • Method : Actions that an object can perform
  • Event : An occurrence (a flag)
  • Encapsulation : Bundling similar functions together, but hiding internal works of a class providing access only through public methods
  • Overloading : defining multiple methods with the same name but different signatures in the same class

Overloading example

Get Sum (int)

{ x + y = z}

Get Sum (short)

When you start to think about data and how you’re managed, access, and manipulate it with .NET think of it as the above defining the context of which you group it.

For example. . . when you want data from a driver’s license you would need to make individual calls to data fields. Do you want to write a function to call each individual field? Do you want to build a list using database connections to query this information per time? Or do you want to create an object that the a class, using methods, could utilize the standard object w/ fields defined and connections configured endless amounts of time. If the object would need to be extended, add new fields with dataconnections extend and then enhance your functionality to the classes w/ methods utilizing these new object references.

 . . . design by Use-Case Scenarios. . . If you review (U)nified (M)odeling (L)anguage you start to deduct the series of classes and methods required to perform the overall functions. (e.g. A DMV case. Updating ID info, renewing information, providing 30 days notice to driver that his/her DL is going to expire, etc)

So you have to review these scenarios possible and plan for ways to handle those requirements for developing your overall .NET plan.

The (5) access modifiers and define where a methos must be located in order to call the method in question?

  • Public — Anything
  • Private — Base Class ONLY
  • Protected — Base & Derived
  • Internal — Base & Assembly
  • Protected Internal — Base, Derived, Assembly

So the syntax to add a field to a class? 

It would be [Access Modifier] [Type] [_nameofField]

private int _nameoffield = 0;

The syntax for adding a method to a class?

[Access Modifier] [field type] [method name] ([field type] [field name])

{

return variable;

}

An example of using a method to calculate data and deliver back to the calling method which returns the value into the messagebox object for display to the user. (using C#)

private INT _nameofField = 0;

x = this.methodname(3,2)

messagebox.show(x)

private int methodname(int num1, int num2)

{

int i = num1 + num2;

return i;

}

—- Now for an overloaded method with the above example would be like this. The overloading will understand which method to use, even though they have the same name, but allow for different amounts and types of data according to the method configuration vs it’s method peers.

private INT _nameofField = 0;

x = this.methodname(3,2)

y = this.methodname(3,2,1)

messagebox.show(x)

private int methodname(int num1, int num2)

{

int i = num1 + num2;

return i;

}

private int methodname(int num1, int num2, int num3)

{

int i = (num1 + num2 + num3) / 3 ;

return i;

}

What are properties used for? to limit a user’s ability to set and/or get information.

So what would you use constructors for? You have these constructors to function at the time an object is instantiated.

As an individual is being requested from a customer database, the constructors aremethods of which we connect to the database, retrieve the data for this method, and making it available for the individual requested.

 ——

Module 5 Notes:

Overriding Base Class? It’s the ability to inherit aspects of a base class, but enhancing the derived class to it’s specific needs. What about Overriding vs Constructors? You want to do both the derived class methods AND do the base class method constructed for the derived class. Overriding will override the base class and only do the derived class method(s). Make sense?

Base Class BankAccount

{

public virtual void deposit(decimal amount)

{}

}

public class SavingsAccount : Bank Account

{

public override void deposit (decimal amount) //overriding the base class, because the base method is “virtual” , which allows overriding

{}

}

BankAccount account = new SavingsAccount(); // Instantiation of new object account using the SavingsAccount which inherits from the Base Class BankAccount

Hiding (C#) /Shadowing (VB) Base Class? It’s the ability to hide aspects of a base class for a derived. You take some aspects from the base class, but you don’t need other aspects so you hide them from your new derived class.