Whats in a Module Steve La IBM Canada

What’s in a Module? Steve La, IBM Canada Session Code: F 1 November 16 10: 15 -11: 15 Platform: DB 2 for LUW

Objectives • Create a module to collect related data types, objects, and procedural logic • Restrict access through only published module interface • Leverage complex data types like rows and arrays in procedural logic • Design logic to handle schema evolution via anchor data types 2

Module Fundamentals 3

What’s a Module? • A module is a database object which can be used like a namespace to encapsulate and reference the definitions and declarations of other objects with a common purpose • Similar to a class in object oriented languages • Allows for modular development, deployment, and management of SQL procedural logic accessing relational tables 4

Module Features • Cleaner external interface definition • Only published objects can be accessed outside of the module • Private objects are only visible to other objects within the module • Simpler authorization management • Authorization checking done at the module level • Execute privilege on a module allows user to access any published objects in that module • Forward declaration of routine prototypes • Allows developer not worry about order of routine declarations • Module initialization • A single global instance of a given module active in a given connection. • Perform setup logic the 1 st time any published object in the module is referenced within the connection 5

Creating a Module >>--CREATE--+----------+--MODULE--module-name------->< '--OR REPLACE—' • A module is created within a schema (either specified or current schema) • If OR REPLACE is specified • the existing module, including all objects within it, is dropped • Existing privileges that were granted on the module are preserved • Module objects are created separately • Allows for more granular and flexible management of module content 6

What can be defined within a Module? • Supported objects • User defined data types including: distinct type, row type, array type, cursor type • Global (within the module) variables • Conditions to enforce business rules • Procedures • Functions • Unsupported objects • Tables • Views • Sequences 7

Defining Objects within a Module >>--ALTER MODULE--module-name-----------> >-+-ADD--+--module-object-definition---+-------+-->< | | '-PUBLISH--+--module-object-definition---+---‘ • Use PUBLISH to define external interface objects that can be referenced outside of the module. • PUBLISH can also be used to declare a forward routine prototype without a body implementation • Use ADD to define support objects that are internal to the module • ADD can also be used to add the implementation body to a previously published routine prototype 8

Module Conditions and Condition Handling • A condition allows for associating a name to an event • Define using ALTER MODULE PUBLISH/ADD <condition_definition> • Optionally associate the event to a SQLSTATE and message • Use the SIGNAL statement to trigger the event • SIGNAL <condition_name> • Use in conjunction with condition handlers to handle the conditions within SQL PL context • DECLARE <handler_type> HANDLER FOR <condition_name> <handling_logic> • Much like throw/catch exception handling in C++, Java 9

Referencing Module Objects • Since a module is defined within a schema, objects within a module can be referenced in several ways • 3 -part name reference • <schema. Name>. <module. Object. Name> • 2 -part name reference • <module. Name>. <module. Object. Name> • CURRENT_SCHEMA used • 1 -part name reference • <module. Object. Name> • Can only be used within a module context 10

Module Initialization • Implement body of special initialization procedure called SYS_INIT to initialize the state of a module • Implicitly invoked when 1 st reference is made to a module object within the connection • Can be implemented as a SQL or external procedure • Must be unpublished and defined without any parameters 11

Privileges on a Module • Privileges only at the module level • Use GRANT EXECUTE ON MODULE statement to grant privilege to reference published objects of a module • execute any published routines defined in the module • read from and write to any published global variables defined in the module • reference any published user-defined types and conditions defined in the module • Corresponding REVOKE EXECUTE ON MODULE statement to revoke privileges 12

Dropping Module Objects • Dropping an object within a module • ALTER MODULE module-name DROP module-object-identification • Dropping a module • DROP MODULE module-name • Will drop all objects inside the module, and then drop the module itself • Dropping the body of a module • ALTER MODULE module-name DROP BODY • Will drop all unpublished objects inside the module, as well as the bodies of all published routines, thereby turning them into prototypes 13

Where to look for what in the Catalog Views • SYSCAT. MODULES to find modules • SYSCAT. MODULEAUTH to find privileges granted on modules • SYSCAT. MODULEOBJECTS to find objects defined in a module and whether an object is published or not • SYSCAT. ROUTINES. MODULEROUTINEIMPLEMENTED to determine if a module routine body is implemented or not • db 2 look -mod generates DDL statements for each module, and for all of the objects that are defined in each module 14

Working Example of a Module 15

Module on Managing IDUG Sessions • Module to group together data types, variables, and corresponding SQL PL logic to manage IDUG sessions • Provide interface to add a session, enforce submission deadline, extract session info, accept and reject sessions • Interaction solely through module interface that encapsulates access to relational tables 16

Table DDL CREATE TABLE sessions (session_number INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title VARCHAR(50) NOT NULL, presenter VARCHAR(20) NOT NULL, speaker_bio VARCHAR(100), abstract VARCHAR(100) NOT NULL, status CHAR(1) NOT NULL, track CHAR(1) , initial_submission TIMESTAMP(0) NOT NULL, last_update TIMESTAMP(0) NOT NULL, first_time_presenter CHAR(1) NOT NULL )% CREATE TABLE past_presenters (presenter VARCHAR(20) NOT NULL PRIMARY KEY, year SMALLINT )% 17

Creating the Module and Initial Set of Prototypes CREATE OR REPLACE MODULE idug% ALTER MODULE IDUG PUBLISH PROCEDURE add_session (in_title VARCHAR(50), in_presenter VARCHAR(20), in_speaker_bio VARCHAR(100) DEFAULT NULL, in_abstract VARCHAR(100) )% ALTER MODULE idug PUBLISH FUNCTION is_first_time_presenter (in_presenter VARCHAR(20)) RETURNS CHAR(1)% VALUES idug. is_first_time_presenter(‘John Doe’)% SQL 20496 N The routine "IDUG. IS_FIRST_TIME_PRESENTER" cannot be invoked because it is only a routine prototype. Create empty module Publish a prototype procedure Publish a prototype function No implementation body yet 18

Making Supporting Routine Private ALTER MODULE idug DROP FUNCTION is_first_time_presenter% ALTER MODULE idug ADD FUNCTION is_first_time_presenter(in_presenter VARCHAR(20)) RETURNS CHAR(1) BEGIN DECLARE first_time CHAR(1); IF (EXISTS (SELECT 1 FROM PAST_PRESENTERS WHERE presenter=in_presenter)) THEN SET first_time = 0; ELSE SET first_time = 1; END IF; RETURN first_time; END% Drop published prototype function Define new private function with body Procedural logic encapsulates relational table access Private function not visible VALUES idug. is_first_time_presenter('John Doe')% SQL 0440 N No authorized routine named “IDUG. IS_FIRST_TIME_PRESENTER" of type "FUNCTION" having compatible arguments was found. 19

Adding Implementation Body to Published Routine ALTER MODULE idug ADD PROCEDURE add_session (in_title VARCHAR(50), in_presenter VARCHAR(20), in_speaker_bio VARCHAR(100) DEFAULT NULL, in_abstract VARCHAR(100) ) BEGIN DECLARE cur_ts TIMESTAMP(0); DECLARE first_time CHAR(1); SET cur_ts = current timestamp(0); SET first_time = is_first_time_presenter(in_presenter); INSERT INTO sessions (session_number, title, presenter, speaker_bio, abstract, status, track, initial_submission, last_update, first_time_presenter) VALUES (default, in_title, in_presenter, in_speaker_bio, in_abstract, 'S', null, cur_ts, first_time); END% Add body to existing published prototype procedure Invoke private function within module context Encapsulate access to relational table 20

Invoking Published Module Routine CALL idug. add_session(in_title=>'What is in a Module? ', in_presenter=>'Steve La', in_abstract=>'Stuff about modules')% CALL idug. add_session(in_title=>'Features Lost in Documentation', in_presenter=>'Steve La', in_abstract=>'SQL stuff')% CALL idug. add_session(in_title=>'More about DB 2', in_presenter=>'John Doe', in_abstract=>'DB 2 stuff')% SELECT session_number, title, presenter FROM sessions% SESSION_NUMBER TITLE ----------------------1 What is in a Module? 2 Features Lost in Documentation 3 More about DB 2 PRESENTER -----Steve La John Doe 3 record(s) selected. 21

Encapsulate Retrieval from Relational Table ALTER MODULE idug PUBLISH FUNCTION extract_sessions() Publish table RETURNS TABLE(description VARCHAR(100)) function of sessions BEGIN DECLARE sqlcode INT DEFAULT 0; DECLARE session_number_v INT; DECLARE title_v VARCHAR(50); Encapsulate access DECLARE presenter_v VARCHAR(20); to relational table DECLARE session_cursor CURSOR FOR SELECT session_number, title, presenter FROM sessions; OPEN session_cursor; fetch_loop: LOOP FETCH session_cursor INTO session_number_v, title_v, presenter_v; IF sqlcode=100 THEN LEAVE fetch_loop; END IF; PIPE ('Session #' || session_number_v || ' "' || title_v || Customize return '" by ' || presenter_v); result set END LOOP fetch_loop; CLOSE session_cursor; RETURN; END% 22

Encapsulate Retrieval from Relational Table (cont. ) SELECT * FROM TABLE(idug. extract_sessions())% DESCRIPTION --------------------------------------------------Session #1 "What is in a Module? " by Steve La Session #2 "Features Lost in Documentation" by Steve La Session #3 "More about DB 2" by John Doe 3 record(s) selected. 23

Initializing Global Variables within Module ALTER MODULE idug ADD VARIABLE submission_deadline TIMESTAMP(0)% Private variable for submission deadline ALTER MODULE idug PUBLISH FUNCTION get_submission_deadline() RETURNS timestamp(0) RETURN submission_deadline% ALTER MODULE idug ADD PROCEDURE sys_init() BEGIN SET submission_deadline = '2015 -03 -24 -23. 59'; END% CONNECT RESET% CONNECT TO IDUGDB% VALUES idug. get_submission_deadline()% 1 ---------2015 -03 -24 -23. 59 Publish deadline Variable initialized in implicit SYS_INIT procedure SYS_INIT only called during on 1 st access to module within connection 24

Signaling Condition when past Deadline ALTER MODULE idug PUBLISH CONDITION past_submission_deadline% Publish specific condition to allow customized error handling in callers ALTER MODULE idug PUBLISH PROCEDURE add_session (in_title VARCHAR(50), in_presenter VARCHAR(20), in_speaker_bio VARCHAR(100) DEFAULT NULL, in_abstract VARCHAR(100) ) BEGIN …. IF (cur_ts > submission_deadline) THEN SIGNAL past_submission_deadline Enforce submission SET message_text = 'Past submission deadline'; deadline and signal END IF; specific condition. . . END% CALL idug. add_session(in_title=>'More about DB 2', in_presenter=>'John Doe', in_abstract=>'DB 2 stuff')% SQL 0438 N Application raised error or warning with diagnostic text: "Past submission deadline". SQLSTATE=45000 Submission not allowed past deadline 25

Leveraging Complex Data Types 26

Regular Array • Allows manipulation of a collection of homogenous elements within SQL procedural context • Can used for SQL variables, routine parameters • Support for array construction, element selection, element assignment • Interface with SQL via ARRAY_AGG and UNNEST functions CREATE TYPE Array. Of. Integers AS INTEGER ARRAY[]% CREATE TYPE Array. Of 1000 Varchars AS VARCHAR(50) ARRAY[1000]% BEGIN DECLARE int. Array. Of. Integers; DECLARE vc. Array. Of 1000 Varchars; …. END% Create array data types Create variables of array data types 27

Differences from C Arrays • Indices for SQL arrays begin at 1 • Maximum cardinality is optional and • not related to the physical representation of the array • used to ensure at runtime that subscripts are within bounds • Actual cardinality is defined when the value is created. • Illegal to reference array element beyond the array’s cardinality CREATE VARIABLE vc. Array. GV Array. Of 1000 Varchars% SET vc. Array. GV = ARRAY['one', NULL, 'three']% VALUES vc. Array. GV[6]% SQL 20439 N Array index with value "6" is out of range or does not exist. SET vc. Array. GV[6] = 'six'% cardinality=3 max_cardinality=1000 Elements at ordinal > cardinality not defined Resulting array [‘one’, null, ‘three’, null, ‘six’]

Array Constructors and Built-in Functions • Constructor by enumeration • SET numbers = ARRAY[‘ 416 -305 -3745’, ‘ 416 -305 -3746’]; • Constructor by query • SET numbers = ARRAY[select phone from customers]; • CARDINALITY(array) - Returns the cardinality of an array • MAX_CARDINALITY(array) – Returns the maximum cardinality of an array • TRIM_ARRAY(array, elements. To. Trim) - Trims a given number of elements from the end of an array 29

Creating Array from Relational Table CUST_ID NAME PHONE LOCATION 17 Joe 416 -305 -3745 Toronto 113 Tom 905 -3747 Ajax 716 Sam 416 -305 -3746 Toronto 5 Jill 905 -723 -1662 Markham 221 Kim 416 -478 -9683 Toronto SELECT location, ARRAY_AGG(phone ORDER BY cust_id) AS PHONES, ARRAY_AGG(cust_id ORDER BY cust_id) AS IDS FROM customers GROUP BY location LOCATION PHONES IDS Toronto [416 -305 -3745, 416 -478 -9683, 416 -305 -3746] [17, 221, 716] Ajax [905 -3747] [113] Markham [905 -723 -1662] [5] 30

Creating Relational Table From Array PHONES IDS [416 -305 -3745, 416 -478 -9683, 416 -305 -3746] [17, 221, 716] SELECT * FROM UNNEST(phones, ids) WITH ORDINALITY as T(phone, id, index); PHONE ID INDEX 416 -305 -3745 17 1 416 -478 -9683 221 2 416 -305 -3746 716 3 31

Associative Array • • Ordered and referenced by index value (instead of by position) Index values are unique and do not have to be contiguous No user-defined upper bound on the number of elements VARCHAR and INTEGER supported as date types of index values CREATE TYPE capitals. Array AS VARCHAR(12) ARRAY[VARCHAR(16)]% BEGIN DECLARE capitals. Array; SET capitals['British Columbia'] = 'Victoria'; SET capitals['Alberta'] = 'Edmonton'; SET capitals['Manitoba'] = 'Winnipeg'; SET capitals['Ontario'] = 'Toronto'; SET capitals['Nova Scotia'] = 'Halifax'; END% Associative array of varchar(12) elements indexed by varchar(16) values The indices are province names and the elements are capital cities 32

Associative Array Built-In Functions • CARDINALITY(array) - returns the number of elements that have a (possibly NULL) value • MAX_CARDINALITY(array) - always returns the NULL value • ARRAY_FIRST(array), ARRAY_LAST(array) - return the lowest and highest index value • ARRAY_NEXT(array, index), ARRAY_PRIOR(array, index) – return the next or previous index value • ARRAY_DELETE(array) – deletes all elements • ARRAY_DELETE(array, index) – delete element with index value • ARRAY_DELETE(array, start, end) – delete elements between index values • ARRAY_EXISTS(array, index) – returns whether index value exists in array 33

Creating Associative Array from Relational Table CUST_ID NAME PHONE LOCATION 17 Joe 416 -305 -3745 Toronto 113 Tom 905 -3747 Ajax 716 Sam 416 -305 -3746 Toronto 5 Jill 905 -723 -1662 Markham 221 Kim 416 -478 -9683 Toronto SELECT location, ARRAY_AGG(cust_id, phone) AS phones FROM customers GROUP BY location; LOCATION PHONES Toronto [(17, 416 -305 -3745), (221, 416 -478 -9683), (716, 416 -305 -3746)] Ajax [(113, 905 -3747)] Markham [(5, 905 -723 -1662)] 34

Creating Relational Table From Associative Array PHONES [(17, 416 -305 -3745), (221, 416 -478 -9683), (716, 416 -305 -3746)] SELECT * FROM UNNEST(phones) as T(id, phone); ID PHONE 17 416 -305 -3745 221 416 -478 -9683 716 416 -305 -3746 35

Row Data Type • Consists of a sequence of fields, each with their own name and data type • Allows for manipulation of record using a single variable in a SQL PL context • Can be used in all statements that deal with rows (e. g. , INSERT, FETCH, SELECT-INTO, etc. ) • Can be passed as parameters to routines User defined row type for an employee with fields (id, name, salary) CREATE TYPE emp. Row. Type AS ROW (id INT, name VARCHAR(20), salary DECIMAL(8, 2))% BEGIN DECLARE emp. Row. Type; Create variables of row type END% 36

Assigning Values to Row Variables SET emp. Row. id = 5; SET emp. Row. salary = 10000; SET emp. Row = new. Hire; SET emp. Row = NULL; SET emp. Row = (5, 'Jane Doe', 10000); SELECT id, name, salary INTO emp. Row FROM employee WHERE id=5; Assigning to individual fields Unassigned fields have the NULL Value Assigning one row variable to another Assigning NULL to entire row Assigning a row value expression to a row variable SELECT, FETCH, VALUES directly into a row variable 37

Arrays of Rows • Allows for easy switching between SQL relational and SQL PL array processing CREATE TYPE array. Of. Emps AS emp. Row. Type ARRAY[]% DECLARE emps array. Of. Emps; ID NAME SALARY 17 Joe 100000. 00 113 Tom 80000. 00 SELECT ARRAY_AGG ((id, name, salary)) AS emps. List INTO emps FROM employee; SELECT * FROM UNNEST(emps) ; EMPS [(17, Joe, 100000. 00), (113, Tom, 80000. 00)] 38

Associative Array to Create a Status Map ALTER MODULE idug ADD VARIABLE status_submitted_code CHAR(1) CONSTANT 'S'% ALTER MODULE idug ADD VARIABLE status_rejected_code CHAR(1) CONSTANT 'R'% ALTER MODULE idug ADD VARIABLE status_accepted_code CHAR(1) CONSTANT 'A'% ALTER MODULE idug ADD TYPE status_map_type AS VARCHAR(30) ARRAY[VARCHAR(1)]% ALTER MODULE idug ADD VARIABLE status_map_type% Associative array to map code to string value ALTER MODULE idug ADD PROCEDURE sys_init() BEGIN DESCRIPTION SET submission_deadline = '2015 -03 -24 -23. 59'; Session #1 "What is in a Module? " SET status_map[status_submitted_code] = 'Submitted'; by Steve La Submitted SET status_map[status_rejected_code] = 'Rejected'; SET status_map[status_accepted_code] = 'Accepted'; Session #2 "Features Lost in END% Documentation" ALTER MODULE idug PUBLISH FUNCTION extract_sessions() by Steve La Submitted RETURNS TABLE(description VARCHAR(100)) Session #3 "More about DB 2" BEGIN by John Doe Submitted … DECLARE status_v CHAR(1); DECLARE session_cursor CURSOR FOR SELECT session_number, title, presenter, status FROM sessions; …. FETCH session_cursor INTO session_number_v, title_v, presenter_v, status_v; …… PIPE ('Session #' || session_number_v || ' "' || title_v || '" by ' || Access array to presenter_v || ' ' || status_map[status_v]); perform mapping … END% SELECT * FROM TABLE(idug. extract_sessions())%

Array of Rows to Process Accept & Rejection ALTER MODULE idug ADD TYPE session. Acceptance. Type AS ROW (session_number INT, title VARCHAR(50), presenter VARCHAR(20), abstract VARCHAR(100), first_time_presenter CHAR(1), accept. Flag CHAR(1))% ALTER MODULE idug ADD TYPE session. Acceptance. Array AS session. Acceptance. Type ARRAY[]% Create row type to hold session info + extra flag Define type for array of such rows ALTER MODULE idug ADD PROCEDURE determine. Which. Sessions. To. Accept (INOUT sessions. Accept. List session. Acceptance. Array) BEGIN INOUT parameter DECLARE index_v INT DEFAULT 1; IN: array of sessions to WHILE (index_v <= CARDINALITY(sessions. Accept. List)) DO process SET sessions. Accept. List[index_v]. accept. Flag = 1; OUT: set flag for accepted SET index_v = index_v+1; sessions END WHILE; Accepts all sessions for END% illustrative purpose 40

Array of Rows to Process Accept & Rejection (cont. ) ALTER MODULE idug ADD PROCEDURE update. Accept. Reject. Sessions (sessions. Accept. List session. Acceptance. Array) BEGIN Input array of rows to DECLARE index_v INT DEFAULT 1; process DECLARE status_v CHAR(1); WHILE (index_v <= CARDINALITY(sessions. Accept. List)) DO IF (sessions. Accept. List[index_v]. accept. Flag = 1) THEN SET status_v = status_accepted_code; ELSE SET status_v = status_rejected_code; END IF; UPDATE sessions SET status = status_v WHERE session_number=sessions. Accept. List[index_v]. session_number; SET index_v = index_v+1; END WHILE; END% Update each session’s status via primary key lookup 41

Array of Rows to Process Accept & Rejection (cont. ) ALTER MODULE idug PUBLISH PROCEDURE process. Sessions. For. Acceptance() BEGIN DECLARE sessions. Accept. List session. Acceptance. Array; Extract tuples from SELECT (ARRAY_AGG(session_number, title, presenter, table into array abstract, first_time_presenter, 0)) INTO sessions. Accept. List FROM sessions; CALL determine. Which. Sessions. To. Accept(sessions. Accept. List); CALL update. Accept. Reject. Sessions(sessions. Accept. List); END% Single array variable passed between subroutines CALL idug. process. Sessions. For. Acceptance()% SELECT * FROM TABLE(idug. extract_sessions())% DESCRIPTION --------------------------------------------------Session #1 "What is in a Module? " by Steve La Accepted Session #2 "Features Lost in Documentation" by Steve La Accepted Session #3 "More about DB 2" by John Doe Accepted 3 record(s) selected. 42

Handling Schema Evolution 43

Changing Data Types ALTER TABLE sessions ALTER COLUMN presenter SET DATA TYPE VARCHAR(25)% Need to increase length to accommodate longer names • Requires subsequent changes to dependent variables • Could be a lot of dependents • Time consuming and error prone ALTER MODULE idug ADD FUNCTION. . . is_first_time_presenter(in_presenter VARCHAR(20)) ALTER MODULE idug PUBLISH PROCEDURE add_session. . . (in_title VARCHAR(50), in_presenter VARCHAR(20), ALTER MODULE idug PUBLISH FUNCTION extract_sessions(). . . DECLARE presenter_v VARCHAR(20); ALTER MODULE idug ADD TYPE session. Acceptance. Type AS ROW. . . (session_number INT, title VARCHAR(50), presenter VARCHAR(20), Need to update all these dependents 44

Anchor Data Type • Specify the data type of one object using the data type of another ‘anchor’ object • Data type change of the anchor object will be recognized and trigger same change in dependent object • Reduce maintenance cost • Application programmer does not need to know the exact details of the source data type • Can also anchor a row type to the schema of an entire table / view • Supported in • • SQL PL routine parameter declarations SQL function return type declarations Creating user-defined types Creating and declaring variables 45

Anchoring Module Objects to Table Columns ALTER MODULE idug PUBLISH PROCEDURE add_session (in_title ANCHOR sessions. title, in_presenter ANCHOR sessions. presenter, in_speaker_bio ANCHOR sessions. speaker_bio DEFAULT NULL, in_abstract ANCHOR sessions. abstract ) ALTER MODULE idug PUBLISH FUNCTION extract_sessions() RETURNS TABLE(description VARCHAR(100)) BEGIN DECLARE sqlcode INT DEFAULT 0; DECLARE session_number_v ANCHOR sessions. session_number; DECLARE title_v ANCHOR sessions. title; DECLARE presenter_v ANCHOR sessions. presenter; ALTER MODULE idug ADD TYPE session. Acceptance. Type AS ROW (session_number ANCHOR sessions. session_number , title ANCHOR sessions. title, presenter ANCHOR sessions. presenter, abstract ANCHOR sessions. abstract, first_time_presenter ANCHOR sessions. first_time_presenter, accept. Flag CHAR(1)) Anchoring of routine parameters Anchoring of variables Anchoring of row fields 46

More Module Examples • Sample modules • ~/sqllib/samples/sqlpl/modules. db 2 • Built-in modules • DBMS_ALERT - register, send, and receive alerts • DBMS_JOB - create, schedule, and manage jobs • DBMS_OUTPUT - put/get text. useful during application debugging to write messages to standard output • DBMS_PIPE - send messages through a pipe between sessions • DBMS_SQL – prepare, describe, execute dynamic SQL • DBMS_UTILITY - various utility routines 47

Summary • Create modules to encapsulate related data types, variables, and procedural logic • Publish only essential objects to define clear external interface • Leverage array and row data types to manipulate relational records in procedural logic • Use anchor type for dependent variable and parameter declarations for automatic handling of data type changes 48

Questions? 49

Steve La IBM Canada sdqla@ca. ibm. com Session F 01 What’s in a Module? Please fill out your session evaluation before leaving!