WebSphere – Edition – Liberty ( v18 ) – Installation

Background

It is good to revisit Java J2EE after a long hiatus for me personally.

WebSphere – Liberty

Thankfully IBM avails WebSphere via its Liberty Edition.

Liberty is free and as such it is very accessible.

Version 18.x.y.z

Download

The current version of WebSphere Liberty is v18.0.0.2 and it is available here.

Please download it.

Extract

Extract the downloaded package.

Copy

Copy the extracted files into a neutral location where it will live and be delivered from.

Install

Install Additional features

Here are the additional features that we will be installing:

  1. AdminCenter
AdminCenter
Command
Command – Linux

bin/installUtility install adminCenter-1.0

Command – Windows

bin\installUtility install adminCenter-1.0

Output

adminCenter-1Dot0_20180720_0145PM

Configuration

Security

Server.xml
Security

    <basicRegistry id='basic'>
      <user name='admin' password='websphere' />
      <user name='guest' password='guest' />
    </basicRegistry>

    <administrator-role>
      <user>admin</user>
    </administrator-role>

    <keyStore 
        id='defaultKeyStore' 
        password='Liberty' 
    />
Features – Enable AdminCenter

     <!-- Enable features -->
    <featureManager>
        <feature>webProfile-8.0</feature>
        
        <!--
            2018-07-20 2:02 PM dadeniji
        -->
        <feature>adminCenter-1.0</feature>
        
    </featureManager>

Control

To control the web server, please launch a command shell and issue commands against the bin folder.

Start

Code

bin\serverStart
Output

serverStart_20180720_0141PM.png

Stop

Code

bin\serverStop
Output

serverStop_20180720_0208PM.png

Web Access

Port Numbers & Host

Server.xml

Get Network Port Numbers and host from server.xml

      <!-- To access this server from a remote client 
       add a host attribute to the following element, 
       e.g. host="*" 
   -->
    <httpEndpoint id="defaultHttpEndpoint"
                  httpPort="9080"
                  httpsPort="9443" 
    />

Username & password

server.xml

Also from server.xml, please get user and password.

    <!-- Define an Administrator and non-Administrator -->
    <basicRegistry id="basic">
      <user name="admin" password="websphere" />
      <user name="guest" password="guest" />
    </basicRegistry>

    <!-- Assign 'admin' to Administrator -->
    <administrator-role>
      <user>admin</user>
    </administrator-role>

Browser

adminCenter_login_20180720_0212PM.png

fisql – Seeing “Carriage Return”

Background

Received email pasted below.

The email indicates that we are seeing Carriage Return in the output of one of unix scripts.

Email

Email Image

fisql_seeingCarriageReturn_email_20180720_1113AM.png

Email Text


 end as f6

,case len(rtrim(ltrim(appt_title_code_name)) + rtrim(ltrim(emp_primary_title)))  ^M^[[A^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[C^[[K

when 0 then null

when null then null

else rtrim(ltrim(emp_primary_title)) + ' - ' + rtrim(ltrim(appt_title_code_name) ^M)

Trouble Shooting

Reviewed the script and saw that it uses fisql.

Dug into the SQL and did not see anything amiss.

Editor ( vi )

Hidden Characters

Launched vi and asked for hidden characters to be shown.

BTW the command is “:set list”

Again, Nada.

Line Length

Finally noticed that the offending line length is longer than others.

Remediation

In your SQL file, please make sure that each line’s length is within 80.

References

  1. systutorials.com
    • fisql
      • fisql (1) – Linux Man Pages
        Link

BitLocker – Configuration – Error – “Unable to find the Reporting Services instance name”

Background

Recently we ran into an “ha ha” moment installing Microsoft BitLocker.

 

BitLocker Administration and Monitoring

Configuring Reports

Here is the “Configuring Reports” window.

ConfigureReports_20180720_1008AM

Error

Error Image

unableToFindTheReportingServicesInstanceNameMSSQLServer

Error Text

Unable to find the Reporting Services instance name <server-name>\MSSQLServer

Trouble Shooting

Reporting Services Configuration Tool ( RSConfigTool )

Launched SQL Server Reporting Services Configuration Tool ( RSConfigTool).

RSConfig_ReportServerStatus_20180720_1033AM.png

Ensured that the service is running and noted the Instance ID.

The Instance ID is SSRS.

Remediation

BitLocker Administration and Monitoring

Configuring Reports

For the instance name, please note the instance name recorded earlier.

That instance name is SSRS.

Configuration SSRSDB.20180720_1041AM

 

 

StackOverflow DB ( 2010 ) – Foreign Keys

Background

Unfortunately, the StackOverflow DB packaged by Brent Ozar and Co does not include indexes and foreign keys.

The premise is that the package is a learning tool and thus encourage the recipient to go in and create useful amenities.

Foreign Key

Our initial effort will be to create Foreign Key Constraints.

Script

Here is the script …


if
     (  object_id('[dbo].[Badges]')  is not null )
 and  (  object_id('[dbo].[Users]')  is not null )
 and  (  object_id('[FK_Badges_User]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Badges]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Users]')
)
begin
    ALTER TABLE [dbo].[Badges]
        ADD CONSTRAINT [FK_Badges_User]
        FOREIGN KEY ([UserId])
        REFERENCES [dbo].[Users]
        ([Id])
end

if
     (  object_id('[dbo].[Comments]')  is not null )
 and  (  object_id('[dbo].[Users]')  is not null )
 and  (  object_id('[FK_Comments_User]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Comments]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Users]')
)
begin
    ALTER TABLE [dbo].[Comments]
        ADD CONSTRAINT [FK_Comments_User]
        FOREIGN KEY ([UserId])
        REFERENCES [dbo].[Users]
        ([Id])
end

if
     (  object_id('[dbo].[PostLinks]')  is not null )
 and  (  object_id('[dbo].[Posts]')  is not null )
 and  (  object_id('[FK_PostLinks_Posts]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[PostLinks]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Posts]')
)
begin
    ALTER TABLE [dbo].[PostLinks]
        ADD CONSTRAINT [FK_PostLinks_Posts]
        FOREIGN KEY ([PostId])
        REFERENCES [dbo].[Posts]
        ([Id])
end

if
     (  object_id('[dbo].[PostLinks]')  is not null )
 and  (  object_id('[dbo].[LinkTypes]')  is not null )
 and  (  object_id('[FK_PostLinks_LinkTypes]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[PostLinks]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[LinkTypes]')
)
begin
    ALTER TABLE [dbo].[PostLinks]
        ADD CONSTRAINT [FK_PostLinks_LinkTypes]
        FOREIGN KEY ([LinkTypeId])
        REFERENCES [dbo].[LinkTypes]
        ([Id])
end

if
     (  object_id('[dbo].[Posts]')  is not null )
 and  (  object_id('[dbo].[PostTypes]')  is not null )
 and  (  object_id('[FK_Posts_PostTypes]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Posts]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[PostTypes]')
)
begin
    ALTER TABLE [dbo].[Posts]
        ADD CONSTRAINT [FK_Posts_PostTypes]
        FOREIGN KEY ([PostTypeId])
        REFERENCES [dbo].[PostTypes]
        ([Id])
end

if
     (  object_id('[dbo].[Votes]')  is not null )
 and  (  object_id('[dbo].[Posts]')  is not null )
 and  (  object_id('[FK_Votes_Posts]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Votes]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Posts]')
)
begin
    ALTER TABLE [dbo].[Votes]
        ADD CONSTRAINT [FK_Votes_Posts]
        FOREIGN KEY ([PostId])
        REFERENCES [dbo].[Posts]
        ([Id])
end

if
     (  object_id('[dbo].[Votes]')  is not null )
 and  (  object_id('[dbo].[Users]')  is not null )
 and  (  object_id('[FK_Votes_Users]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Votes]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Users]')
)
begin
    ALTER TABLE [dbo].[Votes]
        ADD CONSTRAINT [FK_Votes_Users]
        FOREIGN KEY ([UserId])
        REFERENCES [dbo].[Users]
        ([Id])
end

if
     (  object_id('[dbo].[Votes]')  is not null )
 and  (  object_id('[dbo].[VoteTypes]')  is not null )
 and  (  object_id('[FK_Votes_VoteTypes]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Votes]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[VoteTypes]')
)
begin
    ALTER TABLE [dbo].[Votes]
        ADD CONSTRAINT [FK_Votes_VoteTypes]
        FOREIGN KEY ([VoteTypeId])
        REFERENCES [dbo].[VoteTypes]
        ([Id])
end

 

Database Model

DBeaver

Original

DBeaver_Diagram_20180719_1117AM

Revised

DatabaseModel_20180720_0950AM_Revised

 

Source Control

GitHub

DanielAdeniji/StackExchangeDB
Link

 

Foreign Key Constraints – SSMS

Background

Having restored Stack Overflow’s Database version 2008-2010, reviewing the tables and their relationships.

BTW, that post is here.

 

Database Diagram

DBeaver

Original

Image

 

Explanation

  1. No Relationship

 

Create Foreign Key

SSMS

Objective

Let us relate the dbo.Badges table to the dbo.Users table.

The correlating columns are dbo.Badges.UserId and dbo.Users.Id.

Outline

  1. Launch SSMS
  2. Connect to the SQL Server Instance
  3. Choose the database
  4. Select the table that will we will be targeting
    • Access the Keys node
    • Right click on the Keys Node
    • From the drop-down menu, Select “new Foreign-Key…” option
  5. “Foreign Key relationships” window
    • The “Foreign Key relationships” window appears
    • Group Area :- Tables and Column specifications
      • Please zero in on “Tables and Column specifications” group area
      • Click the eclipse ( “…”) button
  6. Tables and Column Window
    • The “Table and Column” window appears
    • Both sides ( Primary and Foreign Key ) show our targeted table
    • Primary
      • Please adjust our primary, the left side as stated below
        • Table Name
          • Users
        • Column Names
          • Id
  7. Exit Designer Window
    • Please press OK as many times as possible to exit the table designer windows
  8. Generate Change Script
    • Please access the menu items ( Table Designer, Generate Change Script )
  9. Save Change Script
    • The “Save Change Script” window appears
  10. Review Generated Script

Images

New Foreign Key

Foreign Key relationships
Display – Original

The original window shows a self relationship, the Badges table related to the Badges table.

 

Tables and Columns
Display – Original

Display – Revised

We want to state that our targeted table is dbo.Badges and the referenced table is dbo.Users.

The columns that participate from the Badges table is UserId.  And, the Id column in the Users table.

Foreign Key Relationships
Display – Revised

Generate Change Script
Save Change Script

 

Database Diagram

Microsoft

SSMS

Image

databaseDiagram_20180719_0453PM

Stack-overflow Database ( Years 2008 through 2010 )

Background

As always looking for sample code and databases.

This time it is a sample database.

Occasionally, new users ask a Database Administrator for learning tools and materials.

 

Stack Exchange

Stack Exchange, the parent company for Stack Overflow, thankfully avails their database has XML files.

Brent Ozar and Co avails the XML files as Microsoft SQL Server Data & log files.

 

Download

Read about downloading the 10 GB data which covers years 2008 thru 2010 from here.

The actual file is  here. And, it is packaged as a 1 GB compressed file.

 

Extract

Extract using 7-Zip.

 

Attach to SQL Server Instance

Launch SSMS and attach data and log file using something like this.

SQL


USE [master]
GO

CREATE DATABASE [StackOverflow2010]
ON
(
	FILENAME = N'C:\Microsoft\SQLServer\Datafiles\StackExchange\Stackoverflow\Stackoverflow.2010\StackOverflow2010.mdf'
)
LOG ON
(
	FILENAME = N'C:\Microsoft\SQLServer\Logfiles\StackExchange\Stackoverflow\Stackoverflow2010\StackOverflow2010_log.ldf'
)

FOR ATTACH

GO

 

Output

 

Converting database 'StackOverflow2010' from version 655 to the current version 869.
Database 'StackOverflow2010' running the upgrade step from version 655 to version 668.
...
...
Database 'StackOverflow2010' running the upgrade step from version 866 to version 867.
Database 'StackOverflow2010' running the upgrade step from version 867 to version 868.
Database 'StackOverflow2010' running the upgrade step from version 868 to version 869. 

Explanation

The version of the packaged database is v2008.

When attached to a later model SQL Server, it is upgraded to that later version.

That explains the output entries “upgrade step from version [m] to version [n].

 

Create Indexes

Based on your query needs please create indexes

Here are targeted indexes for our lone test query

Table – [dbo].[Votes]

Index – [dbo].[Votes].[INDX_UserId_BountyAmount]


use [StackOverflow2010]
go

create index [INDX_UserId_BountyAmount]
on [dbo].[Votes]
(
	  [UserId] asc
	, [BountyAmount] desc
)
with
(
	  FillFactor=100
	, DROP_EXISTING=OFF
)

 

Sample Queries

Query – Users with highest bounties

Query


set transaction isolation level read uncommitted
go

use [StackOverflow2010]
go

; with [cteBountyAmount]
as
(
	select top 15
			  tblV.[UserId]
			, [BountyAmount]
				= sum(tblV.[BountyAmount])

	from [dbo].[Votes] tblV

	group by
		tblV.[UserId]

	order by
		sum(tblV.[BountyAmount]) desc

)

select
		  [UserId]
			= tblU.[Id]

		, [name]
			= tblU.DisplayName

		, [url]
			= tblU.WebsiteUrl

		, [bountyAmount]
			= cteBA.[BountyAmount]

from   [dbo].[Users] tblU

inner join [cteBountyAmount] cteBA

	on tblU.[Id] = cteBA.UserId

order by
	cteBA.[BountyAmount] desc

 

Output

Bigfix – Error – “The specified target is unknown or unreachable”

Background

Trying to configure our BigFix Application Server, but running into the error pasted below.

Error

Image

failedToConnectTheSpecificTargetIsUnknown_20180713_0400PM [BrushedUp

Textual


Failed to connect to https://bigfixsrv:52311:
Windows Error 0x80090303

Remediation

Network

netstat

Command


netstat -an | find "52311"

Explanation

Noticed network connections on our Application’s port ( 52311).

Active Directory

Service Principal Name ( SPN )

setspn

Overview

SPN is formatted as such :-

serviceclass/host:port servicename

Here is a quick breakdown of each element

  1. Serviceclass
    • For Bigfix, the service class registered is iem
  2. Host
    • The computer name
  3. Port
    • If other than the default port number for the application
  4. Service Name
    • It our case it will be the Active Domain Account that our service will be running under

 

Syntax
Syntax – List

REM List SPN entries for computer
setspn -L [computer]

Syntax – Add

REM Add iem account for computer
setspn -S iem/[computer] [user]

Sample

setlocal

set "_comp=BIGFIXSRV"
set "_compFQDN=BIGFIXSRV.LAB"
set "_user=LAB\servicebf"

REM Remove Previous iem account ( Computer )
setspn -D iem/%_comp% %_comp%

REM Remove Previous iem account ( FQDN )
setspn -D iem/%_compFQDN% %_comp%

REM Add iem account (Computer) 
setspn -S iem/%_comp%  %_user%

REM Add iem account  ( FQDN )
setspn -S iem/%_compFQDN% %_user%

REM List spn for computer
setspn -l %_comp%

REM List spn for user
setspn -l %_user%


endlocal

References

  1. IBM
    • Support
      • Pass-Through Authentication failing
        Link
    • IBM Knowledge Center \ SSPS Modeler  \ Configure Single Sign-On
      • The Service Principal Name
        Link
  2. Microsoft
    • Setspn
  3. Viruk67 – You Want It When!
    • Setting the NetworkService as an SPN account
      Link