Live Workshop on 'Cloud Computing for Developers' hosted by IBM

Thursday, October 01, 2009

You might find it late while reading this post since I am posting on the day of the web-workshop. The theme covers a broad array of products aimed at developers/system admins/DBAs, to introduce them to the cloud computing universe.

The workshop is a day long event and has the following agenda :

10:30-Noon Cloud Computing with Amazon Web Services

Speaker: Mike Culver, Amazon Web Services Evangelist
You’ve heard all the hype about why the Cloud is the future – and most likely you have already checked it out yourself. But what can you really accomplish, and why should you even care? In this session, presented by Amazon Web Services, you will learn what AWS is all about, and why companies ranging from the smallest startup to large Wall Street names all deploy in the cloud. You’ll also learn why Amazon Web Services came together with IBM to offer you software. If you’ve been around Enterprise software for any length of time, you know that “by the hour” is not a standard concept, although that's one of the ways you can pay for IBM software in the Amazon Cloud; so to say that innovation was at work is an understatement. This session will end with an extended Question and Answer period to provide you direct access to key team members from Amazon Web Services.

12:00-1:00pm IBM DB2: Using the most flexible enterprise-class database on Amazon EC2
Speakers: Mark Wilding, Leon Katsnelson

IBM DB2 database software running on Amazon EC2 lets you get the most out of today's tough
economic climate that is charaterized by reduced budgets and freezes on capital expenditure. IBM and AWS provide a lot of flexibility for cloud computing, including free DB2 AMIs, low cost by-the-hour production-ready DB2 AMIs, and the ability to bring your existing DB2 licenses to the cloud. With robust capabilities for the most demanding workloads, and tons of autonomic features, DB2 is ideally suited for cloud environments. Join us to learn how to quickly develop and deploy new and existing applications using DB2 and Amazon EC2. This session will walk you through the DB2 AMIs and highlight some of the unique capabilities in DB2, including the new features in DB2 9.7 that make it much easier to move applications from Oracle to DB2.


12:00-1:00pm IBM Informix Dynamix Server (IDS) on Amazon EC2
Speakers: Cindy Fung and Guy Bowerman

Using IBM Informix Dynamic Server (IDS) database software delivers exceptional reliability,
scalability, ease of administration, online transaction processing (OLTP) performance, and a complete range of application development options. Hear and see how you can get started solving business problems in the cloud with IDS as your database for high availability and blazing transaction performance. Like over 100,000 customers worldwide, you too can 'set it and forget it' to focus your attention on delivering business solutions rather than DBA activities. This session will take you through the IDS AMIs and will highlight some of the features of IBM Informix Dynamic Server.


1:00-1:55pm Using WebSphere Application Server on Amazon EC2
Speaker: Ed McCabe, Arthur Meloy

The industry's leading web application server is now officially available on the industry's leading public cloud. Hear and see how you can leverage WebSphere Application Server to develop, deploy and manage your applications within the elastic environment of the Amazon cloud. This session will include demonstrations of how easily you can get started with the WebSphere Application Server AMIs.


1:00-1:55pm Portals and web content management in The Cloud – Less effort, reduce costs
Speaker: Marshall Lamb

Find out how cloud computing can help improve your speed and time to market, reduce your start up effort and costs as well as on going maintenance of your portal and web content management solutions. In this session we'll cover these topics as well as how to get started and how to build solutions using IBM's industry leading IBM WebSphere Portal Server and Lotus Web Content Management running on Amazon Web Services.


2:00-2:55pm Develop a Situational Application with WebSphere sMash on Amazon EC2
Speaker: Michael Fraenkel

WebSphere sMash is a development and execution platform for quickly building agile, web-based applications. Join this session to hear more about how you can use WebSphere sMash to advance your development. This session provides an introduction to developing simple web applications with WebSphere sMash using REST APIs and the Dojo JavaScript toolkit. You will see how to develop with the WebSphere sMash Application Builder in Amazon EC2.


2:00-2:55pm eForms in the Cloud – Optimize Your Business and Solutions
Speaker: Rob Lintern , Rick McCall

eForms can help improve business productivity and streamline business processes. Find out how The Cloud is a perfect and cost effective deployment option for many eForms solutions. This in-depth webinar will offer complete details on getting your Lotus Forms Turbo server up on Amazon Web Services and show how easy it is to create eForms solutions.


3:00-3:55pm Elastic Scaling with WebSphere eXtreme Scale on Amazon EC2
Speaker: Robert Wisniewski

Cloud computing and application hosting within a cloud environment presents exciting opportunities for flexibility and elasticity, while also introducing new and unique challenges in deployment and management. WebSphere eXtreme Scale is an in-memory grid and can help provide this state management for cloud applications with its unique and effective approach to high availability, self healing, and elastic/linear scalability. Join this session to hear more and how you can easily get started along with a demonstration of how you can develop your applications to take advantage of key features such as write-behind caching and synchronous replication.


3:00-3:55pm Mashups in the Cloud – speed up the creation of your business applications
Speaker: Nicole Carrier, Mark Gilmore

Mashups are the perfect situational application for delivering a solutions quickly. Find out how to get started with IBM Mashup Center on Amazon Web Services including getting started, building your mashups, and creating new re-usable solutions to improve your time to value.


4:00-5:00pm Cloud Quickstart- Accelerating your path to cloud computing
Speakers: Christopher Keene, CEO of WaveMaker and Uri Budnik, ISV Partner Program Evangelist

Many developers are unsure how to architect their applications to make the most of cloud computing. In this session, you will learn how IBM's Cloud Quickstart Architecture can accelerate your cloud development. Based on IBM best practices and best-of-breed components from WaveMaker and RightScale, the IBM Cloud Quickstart Architecture makes it fast and easy for anyone to build scalable cloud applications. This session will include a hands-on demonstration showing how to create and deploy cloud applications to WebSphere, DB2 and Amazon EC2 in minutes using WaveMaker and RightScale.


4:00-5:30pm Lotus on AWS: Partner Solutions Show and Tell

Speakers from Ixion LLC, Phurnace Software, Virtuosant, WebRadar, Ascendant Technology,
OpenLogic Join this session to find out how a number of IBM partners are using IBM on AWS to help build and deliver more competitive and compelling solutions for their customers. Each partner will showcase their solution, experience and tips for building cloud solutions with IBM Lotus software on Amazon Web Services. Amazon Machine Images cover include WebSphere Portal Server, Lotus Web Content Management, IBM Mashup Center and Lotus Forms Turbo.

I've signed up for the 12:00-1:00pm IBM DB2: Using the most flexible enterprise-class database on Amazon EC2 . My timezone is Indian and so I am willing to stay up till the middle of the night for this, should be worth it.

10:00-10:30am Becoming a Cloud Programming Pioneer

Doug Tidwell is a Senior Software Engineer in IBM’s Emerging Technology group. He was a speaker at the first XML conference in 1997, and has been working with markup languages, Web services and SOA technologies for many years. His job as a technology evangelist is to explain the standards and technologies behind Cloud Computing and to help customers integrate them into their overall business architectures and strategies. He is the author of many articles on IBM’s developerWorks site (,

10:30-Noon Cloud Computing with Amazon Web Services

Mike Culver, Amazon Web Services Evangelist, Mike Culver joined the Developer Relations Group of Amazon Web Services in 2006, and brings with him twenty years of technology leadership experience, including at companies such as Microsoft. In addition Mr. Culver has a strong background running an IT organization, with over a decade of experience in the Electrical Wholesale Distribution industry. As a Web Services Evangelist at Amazon, he helps developers take advantage of disruptive technologies that are going to change the way we think about computer applications, and the way that businesses compete.

12:00-1:00pm IBM DB2: Using the most flexible enterprise-class database on Amazon EC2 for free

Mark Wilding is Senior Developer for DB2 and Mgr at IBM. Mark is a physics graduate from the University of Western Ontario. Software was always a personal passion until Mark joined the computer science industry in 1994. Before joining IBM, Mark was a security expert and designed one of the first protocol interpreting firewalls but also held roles as a business owner, system/network administrator and a role as a software developer. Mark joined IBM in 1996 and has worked with DB2 LUW ever since. Today, Mark is an STSM at IBM who specializes in DB2, Unix/Linux, software quality, serviceability, reliability, automated support and most recently, cloud computing. Mark is a 3-time author with two books about DB2 (“DB2 for Solaris: The Official Guide” and “DB2 Universal Database in the Solaris Operating Environment”) and his latest on Linux Troubleshooting (“Self-Service Linux: Mastering the Art of Problem Determination”)

12:00-1:00pm IBM Informix Dynamix Server (IDS) on Amazon EC2

Guy Bowerman is the Informix architect responsible for embeddability, virtualization and cloud computing. Based in Beaverton, Oregon, he joined Informix Software in 1994 and has worked in a variety of Technical Support and Development roles in the UK and United States.

1:00-1:55pm Portals and web content management in The Cloud – Less effort, reduce costs

Marshall Lamb is a Senior Technical Staff Member and architect with IBM WebSphere Portal development. His primary areas of focus are large scale deployments, operations, high availability and scalability, and virtualization. He has served as the Chief Programmer on WebSphere Portal V5.0 and V5.1 and has led development teams on several other IBM Software products, including WebSphere Transcoding Publisher, WebSphere Host Publisher, and Communications Server. Marshall has been with IBM since 1991 after graduating from Vanderbilt University with Computer Science and Mathematics degrees.

Brad Schauf is currently a Senior Certified Executive IT Architect in the IBM Software Services for Lotus Portal Practice. Mr Schauf is the technical lead for the Portal NOW and ISSL TransWCM initiatives. Mr Schauf is also a delegate member of the IBM Industry Framework Architecture Board. He recently has been working on creating cloud based offerings for ISSL and specifically WebSphere Portal solutions based on Amazon Web Services.

2:00-2:55pm eForms in the Cloud – Optimize Your Business and Solutions

Rob Lintern is a senior software developer at the IBM Victoria Software Lab in Victoria, British Columbia. He works on the Lotus Forms product suite, specifically in its various integrations with other IBM products. He is also responsible for the creation of the Lotus Forms Turbo AWS images. Rob came to IBM via a human-computer interaction research lab at the University of Victoria and still maintains a keen interest in data visualization and software usability.

Rick McCall, is a development manager on the IBM Lotus Forms product team. Rick is responsible for the development of the Lotus Forms Turbo product and the Lotus Forms Services Platform products. Lotus Forms Turbo product is geared to the business user and is a complete solution to allow for quick form creation, circulation and ultimately results analysis to improve business decisions. This complete solution has proven to be ideal fit to the AWS infrastructure. Rick has been working on the Lotus Forms team for the past three years. Prior to working at IBM he working as a business analyst, project manager and developer at awithin major consulting and IT services organiza

3:00-3:55pm Mashups in the Cloud – speed up the creation of your business applications

Nicole Carrier is Program Director, IBM Mashup Center. Nicole's expertise lies in defining, launching, and managing new products, which have included IBM Mashup Center, the WebSphere Portal Accelerators, WebSphere Dashboard Framework, and WebSphere Portlet Factory. Nicole has over 15 years of product management and IT consulting experience. She joined IBM in January 2006 with the acquisition of Bowstreet, where she was Director of Product Marketing.

Mark Gilmore is a Senior Software Engineer for the server and enabler components of IBM Mashups Center. When not developing cool stuff, Mark enjoys spending time with his 2 young daughters, making BBQ, watching automobile racing, and doing endless projects around his house.

4:00-4:55pm Lotus on AWS: Partner Solutions Show and Tell

Adam Ginsburg is a product manager at IBM who works with Lotus's Portal, Mashups and Forms group. Adam joined IBM as a co-founder of Aptrix which was acquired by IBM in 2003 and is now the basis for Lotus Web Content Management product. Since the acquisition, Adam has had a number of roles including reporting to the Lotus CTO and focusing around bringing portal and web related products and offerings to market. Over the last year Adam's focus has been on cloud computing. Adam is a husband, father of three and a keensurfer. twitter

Steven Gerhardt, V.P. Business Development Ixion LLC, Steven is a certified IBM WebSphere software architect with 12 years of experience in managing web-based software development. He is currently responsible for managing Ixion's business development efforts with IBM's WebSphere and Lotus software products.

Daniel Nelson, VP of Product & Co-Founder for Phurnace Software, Daniel is responsible for all aspects of product management including innovation anproduct line expansion. Daniel has over ten years of experience in the software development field, eight of which are in senior management positions in operations, project management, and quality assurance at innovative early-stage software companies such as Isochron Data,, the Cobalt Group, NetObjects, and SurfWatch Software. He has a B.A. and an M.B.A. from the University of Texas at Austin. His idea and business plan for Phurnace Software, Inc. won him the coveted Moot Corp. Award in 2006 from the University of Texas competition.

Eleanor Anderson-Miles is Chief Marketing Officer for Virtuosant Technology. Ms Anderson-Miles brings over 20 years of healthcare strategic marketing experience to her role as Virtuosant's Chief Marketing Officer. Through her background in sales, product management, and marketing with several healthcare information technology companies, she excels in understanding how to realize market potential. Using extensive research into target markets and audiences, she crafts well-founded branding, messaging, sales approaches, and marketing initiatives that resonate with the marketplace. She has rebranded four healthcare companies, has led the successful integration of 3 product management teams post-merger, and has consistently supported sales efforts resulting in exponential growth. Ms. Anderson-Miles earned her BA and MA from the University of California, Berkeley.

Stephan Beauchesne is Founder and President of Web Presence Architects, a US-based IBM business partner. The firm developed a WPS portlet called WebRadar, a suite of tools designed to enhance the reporting capabilities and Web page editing of IBM's WCM. Previously, Mr. Beauchesne was Web Project Manager for the International Finance Corporation (the private sector arm of the World Bank) for 8 years, where he oversaw the production of internal and external Web sites. He also spearheaded the adoption of Aptrix (precursor to the IBM WCM) as the default Web Content Management solution, still in use today. He also has held positions of Web Manager, Media relations officer and Assistant director of Economic Research at the Nasdaq Stock Market in the 90s. He holds an MBA in Finance and a BA in Economics from McGill Univ

Matt Pomroy, Business Unit Executive, Ascendant Technology. Matt Pomroy leads Ascendant's Software Engineering practice and helps organizations implement complex software solutions utilizing the IBM stacsoftware solutions including WebSphere Portal and WebSphere Application Server. Matt has been instrumental in helping customers improve their software engineering efficiency through a combination people, process and tooling. He worked and continues to work closely with IBM product teams to deliver IBM middleware administration automation with the Rational Automation Framework for WebSphere and is a frequent speaker at Rational, Lotus and WebSphere events.

Alden Taylor, Senior Consultant, Ascendant Technology. Alden is the architect and lead developer of the Rational Automation Framework for WebSphere. He is a consultant with Ascendant Technology, specializing for the previous 6 years on WebSphere and Portal architecture, infrastructure and development. Prior to joining the Rational Automation Framework for WebSphere team, Alden assisted project teams using WebSphere, Rational and Lotus brand products to accomplish project goals and overcome technical challenges. Alden holds a B.S. in Computer Science from the University of Colorado.

1:00-1:55pm Using WebSphere Application Server on Amazon EC2

Ed McCabe, WebSphere Application Server Product Manager, Ed has been in Product and Market Management within the IBM AIM organization for over 10 years now. He has spent the last four years in WebSphere Application Server Product Management recently leading the introduction of the WebSApplication Server AMI offerings, enabling use of WebSphere Application Server within the Amazon EC2 cloud infrastructure.

Arthur Meloy, WebSphere Application Server Software Development Manager, Arthur has been in the IBM WebSphere development and test organization for 8 years. He is the release manager of the WebSphere Application Server Hypervisor Edition and directly assisted in the creation of the WebSphere Application Server v7.0 AMI.

2:00-2:55pm Develop A Situational Application with WebSphere sMash on Amazon EC2

Michael Fraenkel is a Senior Technical Staff Memeber based in Research Triangle Park, North Carolina. He is currently the lead engineer for WebSphere sMash. Michael has been a lead developer and architect for both Lotus Notes and WebSphere Application Server over his 18 year career.

3:00-3:55pm Elastic Scaling with WebSphere eXtreme Scale on Amazon EC2

Rob Wisniewski, Technical Evangelist, WebSphere eXtreme Transaction Processing - Rob is responsible for the interface and relationship between XTP developers/product teams and the customers who consume those products to solve cutting edge business problems in a low cost and scalable way. These responsibilities includes articles, video podcasts, conferences and direct customer engagements such as technical briefings and proof of concept work. His experience as a performance analyst working with WebSphere Application Server has created a unique perspective on the motivation for and creation of this new generation of elastic business solutions.

4:00-4:55pm Cloud Quickstart- Accelerating your path to cloud computing

Christopher Keene is the CEO of WaveMaker Software. WaveMaker is the only Open-source Platform as a Service (OPaaS) for developing cloud-ready applications. WaveMaker is backed by a 10,000-strong open source developer community and has been selected by IBM as part of IBM's Cloud Quickstart Program. Previously, he was the founder and CEO of NASDAQ-listed Persistence Software, and has worked at McKinsey & Company and Hewlett.

Uri Budnik is the Director of Business Development for RightScale and is responsible for the ISV partner program. Prior to RightScale, he co-founded and managed the datacenter infrastructure for a technology company that provides automated podcasting tools to newspapers and magazines. Before that, he held a variety of roles at Qwest Communications in the internet services and data center division including subject matter expert for complex hosting. Uri Budnik won the entrepreneur of the year award from the Marshall Business School of the University of Southern California and started a company based on that award-winning business plans.

Databases and procedural programming - part 1

Tuesday, September 29, 2009

dba : the code is just an UDB translation of the 'whatever' script written in some 'whatever' interpreted language

programmer : well, that was my requirement, was asked to convert this into an db2 SP,

dba : whats the cardinality of this cursor ?

p : 72 million

d : (ok, today I did wake up in front of the mirror) and you think this is good?

p : that was the work set in the file for that script

d : do you know how that handled the file and memory? and do you know this is going to sort all 72 million rows?

p : so what? DB2 has been touted as best thing next to bread, should be a breeze, now you help me speed this up, is this locked?

d : (can't find a gun) hands out the 'thinking in sets' by joe celko can you please read this when free? (should contact Colbert, well there ain't colberts in the nerd domain )

p : ok, but promise you'll speed this procedure

d worked a week and came up with a procedure that worked in sets and wanted to advise this to the procedural DB programmer (along with the link to the famous 'kiss my royal irish a**' scene from 25th hour)

1. Databases are logical mappings of data, not physical, I've even been asked to run an update over one page to the next in the table, and table being called a file

2. projections and other db concepts rose out of set theory, so please think in sets,

3. break your work into finer pieces, the database and tables are not at your disposal

Once I was called to inspect a 'database slowness' for an SP. Below is a snippet.

declare cur1 cursor for select create_dt, client_cd,order_line_num from tab1 order by client_cd, create_dt ;

fetch cur1 into v_create_dt,v_client_cd,v_order_line_num;

if (completed(v_order_line_num,v_client_cd) = 1 )


do the processing for the client and for the particular line number;



end if;

set old_v_client_cd = v_client_cd;

set old_v_order_line_num = v_order_line_num;

while((fetch cur1 into v_client_cd,v_order_line_num) = (old_v_client_cd,old_v_order_line_num))


end while;

This sql was ordering 72 million rows and picking up the max date for a particular order line . Obviously the programmer wasn't updating his knowledge on the presence of the 'partition by' clause, so I re-wrote

update target_table1 set full_amt = (select sum(amt) from table1 t1 where row-number() over(partition by client_cd, order_line_num order by create_dt desc) = 1)

Simple and powerful single query to tackle a whole pseudocode. That is the power of set processing. Viola! the sp completed in 6 minutes compared to 4 hours and stopping the rest of the processes meanwhile.

I could go on and on about the 'legacy' data programmers, and 'architects'. I am not gifted to working with people like james koopman or Cunningham to have a great insight or implement architectures for the programs. But when I face these kinds of crap from programmers and team leads of over 10 years experience, I question whether it is their complacency or lack of the 'push' for improving their knowledge in the domain they work.

Regards to DB2, this has come a long way from a few blogs/a great info center to a thousand webpages, groups, conferences etc.

MDC - a ten thousand foot introduction

Monday, September 28, 2009

We were trying new stuff when we got a chance to work on a new BCU with 48 partitions, and awesome memory (8 GB per node), and tried using the MDC with all partitioning capabilities, in hashing and range. One of my colleagues used a variable high on cardinality and high on quantiles and no frequent values (DB2 terms). The load took more than 1 hour for a million rows, and it grew tremendously in size. Thus we started reading each and every line of the redbook

In my case, I used the year(order_thro_dt) and client_cd . So

Client_cd = 300

Client_cd = 301

Client-cd – 302

Client-cd - 303

The cube gives you an ex if you use year and some frequently used value as dimentions,

The cube gives you an ex if you use year and some frequently used value as dimentions,

Imagine using ssn, the full 1st row in the violet will be for one ssn and one client, 2nd for one ssn and one client. Such a waste and such slow inserts since db2 has to ‘allocate the block when inserting every new value for the dimention’, so one per ssn – 123456789 and one when ssn = 234567890.
Now what happens during deletes is, db2 drops the whole block only when last value is deleted, for example , if we delete all instances of 300, the first row slice will go off(a), and if we delete all the year-2004 values, the 2nd column slice (b) will go off.

So the inserts of a new year like 2009 will make a case for new column dimention block to get created vis a vis new client for the row dimention block.

MDC and reorgs :

The reason why in case of MDC db2 would not need reorging is because the table is organized by dimensions, which are blocks, there will be only one pointer to the block, and not to every row of the table in case of normal indices.
Thus new blocks are added only when the existing ones get filled, for ex 300 has more than one extent of freq values, the 2nd block gets created. In this case, the 2nd and new block will have only one value and rest would all be empty. Thus the waste in space. So extent size or block size is very important in MDC since smaller blocks will make db2 create new ones, link with previous and take up higher cost, and larger ones waste space as given in the above example.

SQLs that can benefit from MDC :

Blocked indices could be thought of as a floor full of a class of people, one having only American citizens , one with Indian citizens and so on. The first row in the room will be filled with say people above 50 years, 2nd with people from 40-50 years , 3rd with people from 30-40 years.
Now if we want to know how many ‘Americans’ are above 50 years old, we just go to the top floor, and count everyone in the 1st row, end of story, and is the same case if we need count of just Indians, just go to the floor below the top and count the entire one.
So in our case, we have loads of sqls with predicates belonging to clients and the year of service,

Select sum(a), count(B) from table1 where client = ? and year_of_service= ?

This is a part of a typical OLAP query, and hence the assumption that mdc helps analysis queries and are not so helpful in transaction dbs where loading and deleting happens every day.
Now if we need to find someone by the name ‘thomas’, he could be an Indian, an American, a Chinese, and so db2 has to go to each floor and ask each guy if his name was Thomas – a very costly operation.

Select sum(a) from table1 where ssn = ?

For helping these kinds of queries, db2 allows normal indices along with MDC specification in a table.

Create index index1 on table1(ssn);

That will have pointers to each row with ssn value in the table. If it is unique, you can even create unique index, piggy back another non-unique column for ‘index only access’.
The above link to the redbook from IBM that gives you in depth review about MDC, when can they be considered, and the space calculation formulae. This entry was supposed to be a plain English introduction to this beautiful feature that can only be challenged by Oracle’s bit mapped index, but even then, DB2’s implementation wins hands down. The following link tells something about the deferred MDC rollout

This post was not aimed to be an indepth analysis or a strict tech post. So am not discussing about the mathematics of MDC considerations or size calculations. Please take a look at the DEFER value in the ’DB2_MDC_ROLLOUT’ variable and using it in SET CURRENT MDC ROLLOUT MODE statement.

MAX of two values - scalar function

I’ve always wanted db2 to have wider array of scalar functions.

Some like first day of month, last day of month are missing, but the most glaring one that many programmers use is the

max(val1,val2) = (val1 > val2, val1, val2),

and that is missing in UDB version 9.5 as of now. Not very sure about the latest 9.7 that boasts to save the world and solve the hunger problem though.

So I created one myself, and am heavily using it in my code so does other developers.

create function DB2ADMIN.MAXTWO(x date, y date)

returns date

begin atomic

if y is null or x >= y then return x;

else return y;

end if;


Overload this for handling other datatypes and use them with descretion.

Posted by Arun Srini at 2:45 AM 0 comments Links to this post  

Remove numbers from a character value - scalar functions

Thursday, August 13, 2009

One developer asked me to help out his project. He wanted to remove the numbers,any special chars in his field values,

eg., '99kirkh-ammet99' to 'kirkhammet' and ' db2' to 'db'

I tried using translate, but didn't know what to do with the space that came in due to the function. Got help from one friend for the same.
Here is the final one : easy for experts, useful for newbies.

VALUES REPLACE(TRANSLATE('999kirk-hammet9 8', '', TRANSLATE('999kirk-hammet9 8',
'#', 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '#')), ' ', '') ;

To separate the command into pieces to explain it,

1. the inner translate :
TRANSLATE('999kirk-hammet9 8','#','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '#')

- converts any characters to #, and ref. the syntax of translate function (new), pads # if final string is smaller than initial. Please read the new syntax. Very useful function.

Result :
'####-######9 8'
2. Outer translate:
This converts all the values (in our example - #, -, 9, 8) to spaces in the value.

Result :
' kirk hammet '

3. Replace :
This replaces the spaces with empty places thus getting us - 'kirkhammet'

If anyone got useful functions or links for the same, please comment.